Sql query to join query results-CodePudding

I have one query result as:

Wname    NewCases
Mon       2
Tues      1
Sat       3

Second query result as:

Wname     FollowUp
Tues         3
Wed          4
Friday       2

I need to write mysql query to get result as:

Wname   NewCases   FollowUp
Mon       2          0
Tues      1          3
Wed       0          4
Fri       0          2
Sat       3          0

my 1st query:

Select   substring(dayname(Dated),1,3) as wname,
         count(RegId) as NewCases 
from     patienttable 
where    Patient_Type='New' 
     and week(Dated)=week(now()) 
group by dayname(Dated) 
order by Dated

2nd query:

Select   substring(dayname(Dated),1,3) as wname,
         count(RegId) as FollowUp
from     patienttable
where    Patient_Type='FollowUp' 
     and week(Dated)=week(now()) 
group by dayname(Dated)
order by Dated

CodePudding user response：

There is no need to join the queries.
You can do it in one query with conditional aggregation:

SELECT SUBSTRING(DAYNAME(Dated), 1, 3) AS wname,
       SUM(Patient_Type = 'New') AS NewCases,
       SUM(Patient_Type = 'FollowUp') AS FollowUp  
FROM patienttable 
WHERE Patient_Type IN ('New', 'FollowUp') AND WEEK(Dated) = WEEK(NOW()) 
GROUP BY DAYNAME(Dated);