Currently, I have a DB table called mytable which looks like this

This needs to be transformed into

What query can I use for this?

I have tried

SELECT result, DATE(startTime), COUNT(result)
  FROM mytable
 GROUP BY DATE(startTime), result;

but that doesnt work properly

CodePudding user response：

You can use Conditional Aggregation grouping only by the date(DATE(startTime)) such as

SELECT DATE(startTime) AS `date`, 
       SUM(result = 'pass') AS `pass`, 
       SUM(result = 'fail') AS `fail`
  FROM mytable  
 GROUP BY 1
 ORDER BY 1 DESC -- if needed

CodePudding user response：

Let's first say: If you are 100% sure you always have exactly these two values "pass" and "fail" and a query with these hard coded conditions will always produce the correct result, then just use the query shown by Barbaros. There will not be a better way in this case. Then you can ignore the rest of my answer.

But let's think out of the box: Such queries are often risky and used because people didn't really think about the possibilities. Hard coded conditions have the big disadvantage that they just ignore other options. Assume there are also entries like "unknown" or "ok" or "nok" in your table. You didn't notice that and now you are missing this information and your query produces incorrect results.

Therefore, if such things can happen, do not use hard coded conditions.

In this case, let's just count the results and group by result:

SELECT DATE(startTime) AS `date`, 
       result,
       COUNT(result) AS resultQty
  FROM mytable  
 GROUP BY DATE(startTime), result
 ORDER BY DATE(startTime) DESC;

This will make sure you will get information for every result that appears in your table. If this outcome doesn't have the intended form, adjusting this could also be done in SQL using a sub query. But I think this will better be done by your application.