sql - count with multiple WHERE condition

Question

I have the table width structure like this:

record  | color| status
--------------------
record1 | blue | x
record1 | blue | y
record2 | red | x
record3 | yellow  | y

I want to count records grouping by 'color' and for each one count 'x' and 'y'. I want to get the results like this:

color=blue, quantity=2, x=1, y=1 color=red, quantity=1, x=1, y=0 color=yellow, quantity=1, x=0, y=1

My query:

SELECT color as clr,
       count(record) as cnt
FROM table
WHERE status IN (x, y)
GROUP BY  week(color)

I print results in php like this:

while ($row= mysql_fetch_assoc($query)){
     echo "color=".$row['clr'].", quantity=".$row['cnt'].", x=".$row['cnt'][0].", y=".$row['cnt'][1];
}

So the problem is that my method of printing results in php does not work.

Yes, I tryed the answer of @Arun Krish and that's what I needed, thank you for attention! — user4540334
– user4540334, Commented Oct 30, 2015 at 10:00

Arun Krish · Accepted Answer · 2015-10-30 09:30:56Z

1

Try like this

Query:

   SELECT color,
   count(color) as quantity,
   LENGTH(GROUP_CONCAT(status)) - LENGTH(REPLACE(GROUP_CONCAT(status), 'x', '')) as 'x',
   LENGTH(GROUP_CONCAT(status)) - LENGTH(REPLACE(GROUP_CONCAT(status), 'y', '')) as 'y'
   FROM table 
   WHERE status IN('x','y')
   GROUP BY color

PHP:

   while ($row= mysql_fetch_assoc($query)){
     echo "color=".$row['color'].", quantity=".$row['quantity'].",x=".$row['x'].", y=".$row['y'];
   }

answered Oct 30, 2015 at 9:30

Arun Krish

2,1531 gold badge12 silver badges15 bronze badges

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

2 Comments

user4540334 Over a year ago

Thank you so much, that's what I needed!

Gordon Linoff Over a year ago

This answer seems much too complicated.

Gordon Linoff · Accepted Answer · 2015-10-30 10:46:15Z

0

Use conditional aggregation:

select color,
       count(*) as quantity,
       sum(status = 'x') as x,
       sum(status = 'y') as y
from t
group by color;

answered Oct 30, 2015 at 10:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

sql - count with multiple WHERE condition

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related