Separate Count for multiple items within a day MySQL

Question

I am trying to display the status of multiple claims from one day at a time. For example, "date1" has 3 claims. Each claim has a different status. How can I display the status count for ALL claims within "date1". Namely, I would like to order my results by earliest to latest date then have three columns with a count for how many claims from that day have that status. The three status columns should add up to the total number of claims that day. This is what I would like it to look like: (St1 - St3 represent unique statuses)

Date      St1 St2 St3
12-02-12 | 1 | 2 | 3
12-03-12 | 2 | 5 | 3
12-04-12 | 7 | 3 | 8

The query I started working with looks like this, but it is entirely incorrect.

SELECT dates, statuses, COUNT(*) AS St1 FROM table_dates GROUP BY dates;

This was my attempt to get the count of only one status. Figured I had to start somewhere. Thank you.

Answer 1

You want to add status to your GROUP BY:

SELECT dates, status, COUNT(*) AS cnt
FROM table_dates
GROUP BY dates, status

This will give your results in a slightly different format from what you asked:

Date       | Status | cnt
2012-12-02 | 1      | 1 
2012-12-02 | 2      | 2 
2012-12-02 | 3      | 3 
2012-12-03 | 1      | 2 
2012-12-03 | 2      | 5 
2012-12-03 | 3      | 3 
2012-12-04 | 1      | 7 
2012-12-04 | 2      | 3 
2012-12-04 | 3      | 8 

This is probably a better format because if you later add new statuses neither your database schema, SQL query, nor client code will need to change.