How to avoid duplicates in my mysql query?

Question

I am trying to create a query which - when executed - will show a date, status (where there are there are several status options) and the number of events on that date - distinguished by status.

I was able to create a query which shows all data I desire, but I am getting repetitions in dates. I think the way to do it is to use the aliases, but I can't figure out the way. I also tried the case statement, but with no success neither.

SELECT cast(event_date AS date) AS date, count(event_status) AS amount, status
FROM events
GROUP BY date, status

Right now this is what I'm getting:

    date    |  amount    |   status   
---------------------------------------
2019-05-07  |    5       |     YES
2019-05-07  |    1       |      NO
2019-05-06  |    4       |     YES
2019-05-05  |    3       |     YES
2019-05-04  |    6       |     YES
2019-05-04  |    2       |    MAYBE

And this is what I want to get:

    date    |  POSITVE   |   Negative    
---------------------------------------
2019-05-07  |    5       |      1
2019-05-06  |    4       |      0
2019-05-05  |    3       |      0
2019-05-04  |    6       |      2

where any status which isnt't equal to 'Yes' is negative. Any suggestions? Just to clarify: my desired output is just one of the options, I know there could be different ways to solve my problem, but this will suit me fine. Thanks

What you describe in the first paragraph of the question that your query should do has nothing to do with your expected output posted at the end. — Joakim Danielson, May 08 '19 at 10:08
My expected output is what I thought that would be one of the ways to go about it. Any other options which will solve the problem will be just as good and welcome. Suppose I suppost to clarify it, therefore I'll edit it — Mal_235, May 08 '19 at 10:11

apomene · Answer 1 · 2019-05-08T10:11:48.750

2

Use case and Sum like:

SELECT cast(event_date AS date) AS date, 
SUM(CASE WHEN status='YES' THEN 1 ELSE 0 END) as POSITIVE,
SUM(CASE WHEN status !='YES' THEN 1 ELSE 0 END) as NEGATIVE
FROM events
GROUP BY date

edited May 08 '19 at 10:11

answered May 08 '19 at 10:07

apomene

14,282
9
46
72

This query will not give the results the topicstarter is after.. the Logic in the second case is wrong as the topicstarter has three status yes, no and maybe – Raymond Nijland May 08 '19 at 10:10

Yogesh Sharma · Accepted Answer · 2019-05-08T10:26:11.533

You can do conditional aggregation :

select cast(event_date AS date) AS date,
       sum(case when  status = 'Yes' then 1 else 0 end) as Positive,
       sum(case when  status <> 'Yes' then 1 else 0 end) as Negative
from events e
group by cast(event_date AS date);

However, mariaDB has shorthand version for that :

select cast(event_date AS date) AS date,
       sum( status = 'Yes' ) as Positive,
       sum( status <> 'Yes' ) as Negative
from events e
group by cast(event_date AS date);

score 1 · Answer 3 · answered May 08 '19 at 10:17

MariaDB -- like MySQL -- treats booleans as integers in a numeric context. I would write this as:

SELECT cast(event_date AS date) AS date, 
       SUM( status = 'YES' ) as Positive,
       SUM( status <> 'YES' ) as Negative
FROM events
GROUP BY cast(event_date AS date)

How to avoid duplicates in my mysql query?

3 Answers3