Count value from table column

Question

first I have a table which is pivot looks like this

pivot_product_Id | productsxx_Id | category_Id | subcategory_Id | color_Id --------------------------------------------------------------------------- 1 | 1 | 1 | 1 | 1 2 | 1 | 1 | 1 | 2 3 | 3 | 1 | 1 | 3 4 | 4 | 1 | 2 | 4 5 | 4 | 1 | 2 | 5 6 | 2 | 2 | 4 | 6 7 | 5 | 2 | 5 | 7

and I have color table like this

color_Id | color | color2 ------------------------------------------ 1 | black | white 2 | blue | orange 3 | white | black 4 | purple | black 5 | black | green 6 | red | black

and my question is in category ID 1 or 2 ... how many black color exist ? Counting from both color and color2 columns

and I tryed something like this but not geting the result I want and need help to create right query.

if(isset($shoes_post_var) || isset($nightwear_post_var)|| isset($outwear_post_var)){ $query3 = "SELECT count(*) FROM pivot JOIN category ON pivot.category_Id = category.category_Id JOIN subcategory ON pivot.subcategory_Id = subcategory.subcategory_Id JOIN color ON pivot.color_Id = color.color_Id JOIN productsxx ON pivot.productsxx_Id = productsxx.productsxx_Id WHERE color IN ('$black') or color2 IN ('$black') AND category IN ('$shoes_post_var','$nightwear_post_var','$outwear_post_var') GROUP BY pivot.color_Id ASC "; $query5 = mysql_query($query3)or die(mysql_errno()); $total = mysql_result($query5, 0); echo ' '.'('.$total.')';}

Create and debug query as a plain text in phpmyadmin, mysql console or your favourite DB management tool. ONLY AFTER you get the query that works - move it to php. The same for questions - if you ask about mysql, remove everything that isn't relevant to query - any php code in this case. — zerkms
– zerkms, Commented Jul 23, 2013 at 22:21
it doesn't make it clear. If you're asking about a sql query - then another irrelevant code makes understanding harder — zerkms
– zerkms, Commented Jul 23, 2013 at 22:53

peterm · Accepted Answer · 2013-07-23 22:31:46Z

1

A possible solution

SELECT COUNT(*) total FROM pivot WHERE category_id IN (1, 2) AND color_id IN ( SELECT color_id FROM color WHERE color = 'black' OR color2 = 'black' )

Here is SQLFiddle demo

answered Jul 23, 2013 at 22:31

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aleks G Over a year ago

You do realise that sub-select is usually much slower than a join.

peterm Over a year ago

@AleksG Usually doesn't mean that it's true in that particular case, does it? Although I agree in general.

Aleks G · Accepted Answer · 2013-07-23 22:25:23Z

0

You only described two tables and asked about a query based on these two tables. This is a matter of a simple join with a simple selection - and a count - something like this:

SELECT count(1) FROM pivot JOIN color ON (pivot.color_id=color.color_id AND 'black' in (color.color, color.color2)) WHERE pivot.category_id = 1

Feel free to change the where clause for other categories.

However your existing code joins 5 tables and uses some other selection criteria. You really do need to ask the right question. Don't try to ask one thing while implying another.

answered Jul 23, 2013 at 22:25

Aleks G

57.5k34 gold badges180 silver badges280 bronze badges

7 Comments

zerkms Over a year ago

DBMS do have optimizations for COUNT(*) while COUNT(1) would be treated as COUNT() applied for the 1 integer literal which is just confusing and doesn't have any real reason to use, or does it?

Aleks G Over a year ago

@zerkms Not sure about optimisation for count(*) but for the past 20 years I've been taught that count(1) is faster. This is due to the fact that for count(*) an index or a table (if no index exists) scan is required to retrieve values, while count(1) users internal row id's - which is quicker. Last time I did a like-for-like comparison on a postgres table with 100 million rows, count(1) was about 5 times faster than count(*)

peterm Over a year ago

@AleksG Did you do such comparison using MySql?

zerkms Over a year ago

"This is due to the fact that for count(*) an index or a table (if no index exists) scan is required to retrieve values" --- it's not. Mysql doesn't do a scan for COUNT(*). "Last time I did a like-for-like comparison on a postgres table with 100 million rows, count(1) was about 5 times faster than count(*)" --- I don't believe that until I see the execution plans for that :-)

zerkms Over a year ago

To be even more precise: there is a special case COUNT_SYM '(' opt_all '*' ')' in mysql's SQL parser. Which (surprise for me!!!) uses Item *item= new (YYTHD->mem_root) Item_int((int32) 0L,1); as a parameter :-) So seems like for mysql COUNT(*) is absolutely identical to COUNT(0)

|

Collectives™ on Stack Overflow

Count value from table column

2 Answers 2

2 Comments

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Related