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)?

Sep 16, 2022 · 3:51 PM UTC

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`
3
6
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
1
1
Nice! Apparently EXTRACT(DATE FROM col) doesn't exist in MySQL, but DATE(col) works instead. So: SELECT DATE(date) AS date, SUM(success) AS success, SUM(IF(success, 0, 1)) AS fail FROM results GROUP by 1 ORDER BY 1 DESC;
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`
1
1
you win! That's brilliant, I didn't even think about using an `IF`!
1
1
more replies
Replying to @aaronpk
using multiple sums with expressions inside would be my solution db-fiddle.com/f/79y9YseZrEc6…