Aaron Parecki

• fred
• Walter
• Karen Huang
• Jo Blo
• Adam DuVander
• Wesley Pribadi

• fred
• Karen Huang
• James Valleroy

• p83.nl
  There are multiple ways to get the right value for fail. Some examples:
  
  SUM(success = 0) as fail
  SUM(1-success) as fail
  COUNT(*)-SUM(success) as fail
  
  Sat, Sep 17, 2022 9:56am -07:00
• Josh Justice mastodon.technology/@CodingItWrong

  @aaronpk I’m gonna do this from now on when I have to write nontrivial sql

  Fri, Sep 16, 2022 4:20pm +00:00
• Aaron Parecki twitter.com/aaronpk
  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;
  Fri, Sep 16, 2022 4:16pm +00:00 (via brid.gy)
• Grand Moff Darth Salt twitter.com/BarnabyWalters
  using multiple sums with expressions inside would be my solution db-fiddle.com/f/79y9YseZrEc6…
  Fri, Sep 16, 2022 4:08pm +00:00 (via brid.gy)
• johnbrayton micro.blog/johnbrayton

  @aaronpk

  select date, sum(case when success = 1 then 1 else 0 end) as success, sum(case when success = 1 then 0 else 1 end) as fail from foo group by date order by date desc;

  Fri, Sep 16, 2022 4:05pm +00:00
• Walter mastodon.sdf.org/@walterhpdx

  @aaronpk I know you're going to have to join the table twice, once where "success = 1" and second where "success = 0", probably where successdate (as date_format(date,'%Y-%m-%d')) = faildate (as date_format(date,'%Y-%m-%d')). But you have to get sums in there, too.

  Now I need to watch this for someone to come in and solve it.

  Fri, Sep 16, 2022 4:02pm +00:00
• Aaron Parecki aaronparecki.com
  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`
  
  https://twitter.com/jkphl/status/1570804284060475392
  Fri, Sep 16, 2022 9:01am -07:00
• Aaron Parecki twitter.com/aaronpk
  Yep it worked perfectly!
  Fri, Sep 16, 2022 4:01pm +00:00 (via brid.gy)
• Joschi Kuphal 吉 twitter.com/jkphl
  😎 did you try it already? it was off the top of my head …
  Fri, Sep 16, 2022 4:01pm +00:00 (via brid.gy)
• Paola Villarreal twitter.com/paw
  Select extract(date from date), sum(success) success, sum(if(success = 0, 1, 0)) fail from tbl group by 1 order by 1
  Fri, Sep 16, 2022 4:00pm +00:00 (via brid.gy)
• Aaron Parecki twitter.com/aaronpk
  you win! That's brilliant, I didn't even think about using an `IF`!
  Fri, Sep 16, 2022 3:59pm +00:00 (via brid.gy)
• Joschi Kuphal 吉 twitter.com/jkphl
  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`
  Fri, Sep 16, 2022 3:58pm +00:00 (via brid.gy)

Other Mentions

• fred1m twitter.com/fr3d1m
  RT @aaronpk@aaronparecki.com 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)? aaronparecki.com/2022/09/16/6/s…
  
  Sat, Sep 17, 2022 1:57pm +00:00 (via brid.gy)