CODEWITHSUNDEEP
mysqlsql
Muhammad Danish Khan
Muhammad Danish Khan

Reputation: 459

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.

Upvotes: 2

Views: 249

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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;

Upvotes: 1

Piyush Gupta
Piyush Gupta

Reputation: 2179

I think Always executing last query so you should write like this way,

(first Query) UNION ALL (second query);

Try this Query

(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 ALL

(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);

Upvotes: 2

Mithlesh Upadhyay
Mithlesh Upadhyay

Reputation: 196

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows.

UNION ALL does not remove duplicate rows.

Upvotes: 0

Related Questions