CODEWITHSUNDEEP
mysqlsql
mahen3d
mahen3d

Reputation: 7774

Get a Value for Empty Date in MySQL Date function

I have following MySQL query 

SELECT SUM( mc_gross ) AS total, MONTH( transaction_datetime ) AS months 
FROM users_payment 
WHERE 1 AND YEAR( transaction_datetime ) = '2020' 
GROUP BY MONTH( transaction_datetime ) , YEAR( transaction_datetime )

the output i get is 

total month
34.90 2
4.95 3
49.85 4
14.85 5

However, i like to see if i can create below output, just from the MySQL query ?

total month
0.00 1
34.90 2
4.95 3
49.85 4
14.85 5
 0.00 6
 0.00 7
 0.00 8
 0.00 9
 0.00 10
 0.00 11
 0.00 12

Upvotes: 0

Views: 66

Answers (1)

The Impaler
The Impaler

Reputation: 48875

You can use a RIGHT JOIN against a table expression with all 12 values.

For example:

select
  coalesce(q.total, 0.0) as total,
  m.n as month
from ( -- your query here
  SELECT SUM( mc_gross ) AS total, MONTH( transaction_datetime ) AS months
  FROM users_payment 
  WHERE 1 AND YEAR( transaction_datetime ) = '2020' 
  GROUP BY MONTH( transaction_datetime ) , YEAR( transaction_datetime )
) q
right join (
  select 1 as n 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
  union all select 10 union all select 11 union all select 12
) m on q.months = m.n
order by m.n

Upvotes: 1

Related Questions