CODEWITHSUNDEEP
mysql
VikramK
VikramK

Reputation: 663

MYSQL: Get zero values in group by clause

I need zero values also in group by clause .Have read almost all question related to this on Stackoverflow, but none of the solutions have worked.

My Table is

enter image description here

Need to get sum of score grouped by day of month.But I am not getting zero against the days not present in the table

SELECT SUM(engagement_score), DAY(creation_dt)
FROM qee_emp_engagement_index
RIGHT JOIN (
SELECT 1 AS index1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS a ON a.index1 =  DAY(creation_dt)
WHERE org_id = 1
GROUP BY a.index1
ORDER BY a.index1 ASC

Upvotes: 0

Views: 261

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would write the query using a left join rather than a right join (the logic of left join makes more sense to me: keep all the rows in the first table). But, your problem is the where clause. That logic should go in the on clause:

SELECT COALESCE(SUM(eei.engagement_score), 0), a.index1
FROM (SELECT 1 AS index1 UNION ALL
      SELECT 2 UNION ALL
      SELECT 3 UNION ALL
      SELECT 4 UNION ALL
      SELECT 5 UNION ALL
      SELECT 6 UNION ALL
      SELECT 7 UNION ALL
      SELECT 8 UNION ALL
      SELECT 9
     ) a LEFT JOIN
     qee_emp_engagement_index eei
     ON a.index1 =  DAY(eei.creation_dt) AND eei.org_id = 1
GROUP BY a.index1
ORDER BY a.index1 ASC;

In addition, the GROUP BY and SELECT should use the column from the driving table.

Upvotes: 1

Tobias Baumeister
Tobias Baumeister

Reputation: 2147

SELECT DATE(creation_date), SUM(engagement_score) FROM table_name GROUP BY DATE(creation_date)

There is no need to do that weird right join in your code

If you want to group it by the day of month and not by the date, use DAY() instead of DATE()

EDIT: If you want a zero when the day of month doesn't exist, try this:

    SELECT d, SUM(score) FROM
 ((SELECT creation_date AS d, engagement_score AS score FROM table_name) 
UNION ALL
(SELECT d, 0 AS engagement_score FROM table_name WHERE d BETWEEN 1 AND 31)) 
GROUP BY d

Upvotes: 0

Apurv
Apurv

Reputation: 3753

You can do right join between your table and a temp table containing all dates in a range.

Dates can be generated using below query

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2015-01-01' and '2017-01-01'

Upvotes: 0

Related Questions