joined two queries with union all returned single value only?

Question

i have merge two queries from where its returning 3 columns (month, count, targets) by executing both queries separately it returns me 2 records which is correct. but with UNION ALL its return me only single value:

My Query:

select s.month,COALESCE(max(s.count), NULL, 0) as count, COALESCE(max(s.targets), NULL, 0) as target from 
(SELECT MONTHNAME(tm.meeting_date) as month, count(DISTINCT tm.meeting_id) as count, NULL as targets 
FROM tig as t 
left join tig_meeting as tm ON t.tig_code = tm.tig_code 
WHERE tm.meeting_date IS NOT NULL AND tm.meeting_date != 0 AND t.school_district = 'Islamabad C3' 
AND (tm.meeting_date BETWEEN "2018-07-01" AND "2018-09-30") AND tm.key_support <> 'QuarterlyTigMeetings' AND tm.key_support <> 'QTIG_by_SSA' 
AND tm.key_support <> 'MTIG_by_SSA' AND tm.meeting_no NOT LIKE '%RF' 
GROUP BY MONTH(tm.meeting_date) 
UNION ALL 
SELECT mpt.month, NULL as count, SUM(mpt.monthly_target) as targets 
FROM meeting_plan_targets mpt 
LEFT JOIN meeting_plans mp ON mp.id = mpt.meeting_plan_id 
WHERE mp.district = 'Islamabad C3' AND mp.module_title = 1 AND mp.year = 2018 AND mp.quarter = "Quarter 4" GROUP BY mpt.month) s 
group by MONTH(s.month)

for reference please check attached image

That's quite a huge query for us to digest without some context. — tadman, Nov 08 '18 at 05:52
@Barmar, from my first query there is not column of target, while in 2nd query there is no count column. thats why i use null. — Muhammad Khurshid, Nov 08 '18 at 07:06
Now getting another issue. the months are showing in Desc order in Graphs (chart) ??? — Muhammad Khurshid, Nov 08 '18 at 07:06
I meant why do you write `COALESCE(max(s.count), NULL, 0)` instead of just `COALESCE(max(s.count), 0)`? That `NULL` makes absolutely no difference. I wasn't asking about `NULL as count`, I understand why that's needed for the `UNION`. — Barmar, Nov 08 '18 at 16:03

Fahmi · Accepted Answer · 2018-11-08T07:30:56.453

0

You can try below

select s.`month`,COALESCE(max(s.count),0) as count, COALESCE(max(s.targets),0) as target from 
(
SELECT MONTHNAME(tm.meeting_date) as `month`,MONTH(tm.meeting_date) as monnum,count(DISTINCT tm.meeting_id) as count, NULL as targets 
FROM tig as t 
left join tig_meeting as tm ON t.tig_code = tm.tig_code 
WHERE tm.meeting_date IS NOT NULL AND tm.meeting_date != 0 AND t.school_district = 'Islamabad C3' 
AND (tm.meeting_date BETWEEN '2018-07-01' AND '2018-09-30') AND tm.key_support <> 'QuarterlyTigMeetings' AND tm.key_support <> 'QTIG_by_SSA' 
AND tm.key_support <> 'MTIG_by_SSA' AND tm.meeting_no NOT LIKE '%RF' 
GROUP BY MONTHNAME(tm.meeting_date),MONTH(tm.meeting_date)

UNION ALL 

SELECT MONTHNAME(mpt.month), MONTH(mpt.month),NULL as count, SUM(mpt.monthly_target) as targets 
FROM meeting_plan_targets mpt 
LEFT JOIN meeting_plans mp ON mp.id = mpt.meeting_plan_id 
WHERE mp.district = 'Islamabad C3' AND mp.module_title = 1 AND mp.year = 2018 AND mp.quarter = 'Quarter 4' 
GROUP BY MONTHNAME(mpt.month),MONTH(mpt.month)
) s 
group by s.`month`,monnum order by monnum

edited Nov 08 '18 at 07:30

answered Nov 08 '18 at 05:59

Fahmi

37,315
5
22
31

this query gives me months name in descending order. e.g (December, November, October) why this ? – Muhammad Khurshid Nov 08 '18 at 07:07
becuase it's month name so it gives you apphabetic, however I've edited the answer you can check now @MuhammadKhurshid – Fahmi Nov 08 '18 at 07:11
nope, now its shows wrong values. (Month = NULL, Count = 0, Target = 42, August = 20, Target = 0, September = 20, Target = 0) – Muhammad Khurshid Nov 08 '18 at 07:17
(Month = NULL, Count = 0, Target = 42, August = 20, Target = 0, September = 20, Target = 0) – Muhammad Khurshid Nov 08 '18 at 07:27
Thanx for cooperation, i have just added this **ORDER BY MONTH(s.`month`)** in the end of the query, and it works. thankx @fa06 – Muhammad Khurshid Nov 08 '18 at 07:35
@MuhammadKhurshid That won't work for the same reason that `GROUP BY MONTH(s.month)` didn't work. – Barmar Nov 08 '18 at 16:04

score 0 · Answer 2 · answered Nov 08 '18 at 05:59

0

s.month is a month name. MONTH(s.month) won't work, because the MONTH() function expects its argument to be a date or datetime (or a string that can be parsed as a date or datetime), not a month name.

Use GROUP BY s.month instead.

answered Nov 08 '18 at 05:59

Barmar

741,623
53
500
612

joined two queries with union all returned single value only?

2 Answers2