Forum Discussion
Combine two queries
I need to combine these two queries to receive one result:
Here is the first query
with cte as (
select ID,
assessmentdate,
Jurisdiction,
name,
studentid,
submitdate,
count(*) over (partition by studentid) as dup_count
from tblTest
)
Select m.assessmenttype, Count(m.studentid) As referredforassessment
From tblProfile m
Inner Join cte s on
m.studentid = s.studentid
Where dup_count > 1
and s.SubmitDate between '06/01/2024' and '06/30/2024'
and countyid = 6
Group by assessmenttype
Order by assessmenttype
Here are the results from this query:
assessmenttype | referredforassessment |
2 | 3 |
3 | 1 |
7 | 1 |
Here is the second query:
select assessmenttype,
SUM(CASE WHEN referredforassessment = 1 THEN 1 ELSE 0 END) as referredforassessment
from tblProfile
where SUBMITDATE >= '06/01/2024' and SUBMITDATE < '06/30/2024' and countyid = 6
Group By assessmenttype
order by assessmenttype
Here are the results from the second query:
assessmenttype | referredforassessment |
2 | 2 |
4 | 1 |
Here are my desired results:
assessmenttype | referredforassessment |
2 | 5 |
3 | 1 |
4 | 1 |
7 | 1 |
- Hey tbumbray, have you tried using the union operator to join both queries?
with cte as (
select ID,
assessmentdate,
Jurisdiction,
name,
studentid,
submitdate,
count(*) over (partition by studentid) as dup_count
from tblTest
)
Select m.assessmenttype, Count(m.studentid) As referredforassessment
From tblProfile m
Inner Join cte s on
m.studentid = s.studentid
Where dup_count > 1
and s.SubmitDate between '06/01/2024' and '06/30/2024'
and countyid = 6
Group by assessmenttype
--Order by assessmenttype
union
select assessmenttype,
SUM(CASE WHEN referredforassessment = 1 THEN 1 ELSE 0 END) as referredforassessment
from tblProfile
where SUBMITDATE >= '06/01/2024' and SUBMITDATE < '06/30/2024' and countyid = 6
Group By assessmenttype
order by assessmenttype- It's not exactly working the way it should. I want it to add the referredforassessment numbers from both queries if there is an assessmenttype is found for both.
Please refer to my the results I provided in my initial post.
Thanks.
