Sql query to join query results

Question

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

forpas · Accepted Answer · 2022-05-07 09:55:55Z

-1

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

answered May 7, 2022 at 9:55

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql query to join query results

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related