Mysql query with group by?

Question

Table users :
id      (int)
status  (int)
regdate (datetime)

I want to get the results in order of regdate, with count(id) 's and group by status.

For example :

 Date           Count(status = 3)    Count(status = 4)   Count(status = 5)
 
2014-02-24            2                   5                     8 
2014-02-25            2                   5                     8

We should have get the results in only a line per day.

Thanks too much in advance.

that's called a pivot query, and mysql doesn't support them directly. the workarounds are ugly and get EXTREMELY unmaintainable VERY quickly. Fetch the query normally and do the col->row transformation in your client-side code. — Marc B
– Marc B, Commented Feb 28, 2014 at 20:59

M Khalid Junaid · Accepted Answer · 2014-02-28 21:00:29Z

2

You can use SUM() with condition if there are limited statutes like 3,4,5,the expression in SUM() are evaluated as boolean , for n no. of statutes look at Marc B's comments

SELECT
regdate,
SUM(status = 3),
SUM(status = 4),
SUM(status = 5)
FROM `table`
GROUP BY regdate
ORDER BY regdate

answered Feb 28, 2014 at 21:00

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

1 Comment

M Khalid Junaid Over a year ago

@vural can you add the sql fiddle example of your table definitions and sample dataset

Collectives™ on Stack Overflow

Mysql query with group by?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related