CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 343

MySQL: Count if and Group By

I have the following mysql table Test_Results

| ID | Test_ID | Device_OS| Status | Date    |
| 1  | 1       | Android  | passed | 2015/10 |
| 2  | 1       | Android  | failed | 2015/10 |
| 3  | 15      | iOS      | passed | 2015/11 |
| 4  | 20      | Android  | passed | 2015/11 |
| 5  | 27      | Android  | passed | 2015/11 |
| 6  | 10      | iOS      | failed | 2015/12 |
| 7  | 5       | Android  | passed | 2015/12 |

I need to count the ount of unique Test_ID's per OS and group by Month. I wrote a query that counts the general number of unique tests:

SELECT Run_Date, COUNT(DISTINCT Test_ID) FROM Test_Results
GROUP BY MONTH(Run_Date)
ORDER BY Run_Date DESC;

But I cannot figure out how to break by Deivce_OS. So the result will look some thing like this:

Run_Date    COUNT Android iOS
12/10/2015  3650  3650    2500
11/5/2015   1909  1909    1900
10/1/2015   1667  1667    1325

Thank you!

Upvotes: 1

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

First, you shouldn't group by the month without the year -- unless you really, really intend that. Second, you should get in the habit of only using expressions in the SELECT that are in the GROUP BY.

Third, I think you just need conditional aggregation, which looks like this:

SELECT YEAR(Run_Date), MONTH(Run_Date), COUNT(DISTINCT Test_ID),
       SUM(Device_OS = 'Android') as Android, SUM(Device_OS = 'iOS') as iOS
FROM Test_Results
GROUP BY YEAR(Run_Date), MONTH(RUn_Date)
ORDER BY YEAR(Run_Date) DESC, MONTH(RUn_Date) DESC;

EDIT:

I notice you want unique tests per month for the OS's. For this, use COUNT(DISTINCT) in a conditional way:

SELECT YEAR(Run_Date), MONTH(Run_Date), COUNT(DISTINCT Test_ID),
       COUNT(DISTINCT CASE WHEN Device_OS = 'Android' THEN Test_ID END) as Android,
       COUNT(DISTINCT CASE WHEN Device_OS = 'iOS' THEN Test_ID END) as iOS
FROM Test_Results
GROUP BY YEAR(Run_Date), MONTH(RUn_Date)
ORDER BY YEAR(Run_Date) DESC, MONTH(RUn_Date) DESC;

Upvotes: 2

Related Questions