Question 112696 in Mysql Question posted in
The official MySQL documentation can be found here.

Create separate columns after grouping by a particular column in MySQL

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

id startTime result
100 2022-11-17 06:19:00 pass
101 2022-11-17 07:19:00 fail
102 2022-11-17 08:44:00 pass
103 2022-11-17 16:19:00 fail
104 2022-11-16 06:11:00 pass
105 2022-11-16 06:11:00 fail
106 2022-11-16 06:12:00 pass
107 2022-11-16 12:11:00 pass

This needs to be transformed into

date pass fail
2022-11-17 2 2
2022-11-16 3 1

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search