Any clever SQL people out there? Given the raw data on the left, (a table of test results) how do I write a SQL query to give me the results on the right, (the results grouped by date as well as grouped by result)?
4
1
4
That was fast, we have a winner! SELECT `date`, SUM(IF(success, 1, 0)) AS `success`, SUM(IF(success, 0, 1)) AS `fail` FROM `results` GROUP BY `date`
Joschi Kuphal 吉 @jkphl
16 Sep 2022
Replying to @aaronpk
something like this or similar (untested)?: SELECT `date`, SUM(IF(success, 1, 0)) AS `success`, SUM(IF(success, 0, 1)) AS `fail` FROM t GROUP BY `date`

Sep 16, 2022 · 4:01 PM UTC

3
6
Replying to @aaronpk
Who needs StackOverflow when you have Twitter?
1
my problem was I couldn't even figure out how to ask this with words so I wasn't sure what to search for!
Replying to @aaronpk
Actually, the first IF isn't necessary, and I didn't care about stripping the time part, so @paw's suggestion is a bit more accurate:
Paola Villarreal @paw
16 Sep 2022
Replying to @aaronpk
Select extract(date from date), sum(success) success, sum(if(success = 0, 1, 0)) fail from tbl group by 1 order by 1
Replying to @aaronpk
I'm late to the party, but in case anyone finds this and is using a flavor of SQL that doesn't have IF SELECT date, SUM(CASE WHEN success = 1 then 1 else 0 end) AS success, SUM(CASE WHEN success = 0 then 1 else 0 end) AS fail FROM results GROUP BY date