Muhammad Danish Khan Muhammad Danish Khan - 4 months ago 9
SQL Question

Union query return unexpected result

I have combined two queries to get my required result, but it is not giving the result i want.

The queries are like this

SELECT COALESCE(SUM(rd.AMOUNT),0) as total from revenue_d AS rd JOIN revenue_m AS rem on rd.RV_ID = rem.RV_ID Where MONTH(rd.SCROLL_DATE) = 5 and YEAR(rd.SCROLL_DATE)= 2016

Union

SELECT COALESCE(SUM(rd.AMOUNT),0) as total from revenue_d AS rd JOIN revenue_m AS rem on rd.RV_ID = rem.RV_ID Where MONTH(rd.SCROLL_DATE) = 6 and YEAR(rd.SCROLL_DATE)= 2016;


The result it is giving me is like this

+---------------+
| total |
+---------------+
| 0 |
+---------------+


While i want like this

+---------------+
| total |
+---------------+
| 0 |
+---------------+
| 0 |
+---------------+


Even used Union ALL but still getting the same result.

Answer

You need union all. union removes duplicates, so:

SELECT COALESCE(SUM(rd.AMOUNT),0) as total
from revenue_d rd JOIN
     revenue_m rem
     on rd.RV_ID = rem.RV_ID
Where MONTH(rd.SCROLL_DATE) = 5   and YEAR(rd.SCROLL_DATE)= 2016

Union all

SELECT COALESCE(SUM(rd.AMOUNT),0) as total
from revenue_d rd JOIN
     revenue_m rem
     on rd.RV_ID = rem.RV_ID
Where MONTH(rd.SCROLL_DATE) = 6 and YEAR(rd.SCROLL_DATE)= 2016;

However, it is more natural to write this query using group by, although periods with no values will be missing:

SELECT YEAR(rd.SCROLL_DATE) as yyyy,
       MONTH(rd.SCROLL_DATE) as mm,
       COALESCE(SUM(rd.AMOUNT), 0) as total
from revenue_d rd JOIN
     revenue_m rem
     on rd.RV_ID = rem.RV_ID
Where MONTH(rd.SCROLL_DATE) IN (5, 6) and YEAR(rd.SCROLL_DATE)= 2016;

Or, using conditional aggregation (which puts the values in two columns:

SELECT YEAR(rd.SCROLL_DATE) as yyyy,
       SUM(CASE WHEN MONTH(rd.SCROLL_DATE) = 5 then rd.AMOUNT else 0) as total_05,
       SUM(CASE WHEN MONTH(rd.SCROLL_DATE) = 6 then rd.AMOUNT else 0) as total_06
from revenue_d rd JOIN
     revenue_m rem
     on rd.RV_ID = rem.RV_ID
Where MONTH(rd.SCROLL_DATE) IN (5, 6) and YEAR(rd.SCROLL_DATE) = 2016;
Comments