CODEWITHSUNDEEP
sql-server
chad stratman
chad stratman

Reputation: 11

Getting the sum of two select count statements

I've got two select queries I need to combine into one result.

/*  Count of all classes students have passed   */
/* A */
SELECT COUNT(TECH_ID) as Number1, ct.SUBJ, ct.COU_NBR
FROM [ISRS].[ST_COU] st
JOIN [ISRS].[CT_COU_SQL] ct
ON st.COU_ID = ct.COU_ID
WHERE GRADE = 'A' OR Grade = 'B' or Grade = 'C'
GROUP BY ct.SUBJ, ct.COU_NBR

/* Total Count of all students who needed to take a course */
/* B */
SELECT COUNT(TECH_ID) as Number2, ec.SUBJ, ec.COU_NBR
FROM [dbo].[ST_MAJOR_COMMENT] st JOIN [dbo].[Emphasis_Class] ec
ON st.MAJOR = ec.Emphasis
GROUP BY ec.SUBJ, ec.COU_NBR

I need SUBJ, COU_NBR, sum(B - A)

Upvotes: 1

Views: 126

Answers (2)

Bohemian
Bohemian

Reputation: 424993

Use union:

select sum(Number1) from (
   SELECT COUNT(TECH_ID) as Number1
   -- onit all other columns from select then rest of query 1
   UNION
   -- just the count column selected then rest of query 2
) x

This syntax will work with most databases.

Upvotes: 1

radar
radar

Reputation: 13425

you can have your sql queries in the ctes and then do a join and group by

with cte1
as
(
  first query
)
, cte2
as
(
  second query
)
select cte1.subj, cte1.cou_nbr, sum(cte2.number2 - cte1.number1) as difference
from cte1
join cte2
on cte1.subj = cte2.subj
and cte1.cou_nbr = cte2.cou_nbr
group by cte1.subj, cte1.cou_nbr

Upvotes: 1

Related Questions