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

​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:assessmenttypereferredforassessment233171 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: assessmenttypereferredforassessment2241 Here are my desired results: assessmenttypereferredforassessment25314171   Read More

​