Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 adding case to a multiple count statement

Author  Topic 

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-09 : 02:25:19
Hello, I need to add a case statement to my query that based on the outcome of each case statement will produce an overall single outcome of (yes or no). So im couting the number of classes a particular student has taken in each category (math, language, science, etc) and they need to be at a certain number in each category i.e., each category needs to be at => 6. Finally if they meet each category with => 6 They recieve a Yes. So this is my query thus far and im struggling with the syntax for those case statements...PLEASE HELP





SELECT (SELECT COUNT(HIS_1.CN) AS COUNT1
FROM CRS INNER JOIN
HIS AS HIS_1 ON CRS.CN = HIS_1.CN INNER JOIN
STU ON HIS_1.PID = STU.ID
WHERE (STU.ID = @ID) AND (CRS.U1 = 'A')) AS HISTORY,
(SELECT COUNT(HIS.CN) AS COUNT2
FROM CRS AS CRS_1 INNER JOIN
HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN
STU AS STU_1 ON HIS.PID = STU_1.ID
WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'B')) AS ELA,
(SELECT COUNT(HIS.CN) AS COUNT2
FROM CRS AS CRS_1 INNER JOIN
HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN
STU AS STU_1 ON HIS.PID = STU_1.ID
WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'C')) AS MATH,
(SELECT COUNT(HIS.CN) AS COUNT2
FROM CRS AS CRS_1 INNER JOIN
HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN
STU AS STU_1 ON HIS.PID = STU_1.ID
WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'D')) AS SCIENCE,
(SELECT COUNT(HIS.CN) AS COUNT2
FROM CRS AS CRS_1 INNER JOIN
HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN
STU AS STU_1 ON HIS.PID = STU_1.ID
WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'E')) AS FL,
(SELECT COUNT(HIS.CN) AS COUNT2
FROM CRS AS CRS_1 INNER JOIN
HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN
STU AS STU_1 ON HIS.PID = STU_1.ID
WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'F')) AS VA,
(SELECT COUNT(HIS.CN) AS COUNT2
FROM CRS AS CRS_1 INNER JOIN
HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN
STU AS STU_1 ON HIS.PID = STU_1.ID
WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'G')) AS Prep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-09 : 06:02:43
why not wrap all in a single select?
like


SELECT COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep
FROM CRS INNER JOIN
HIS ON CRS.CN = HIS.CN INNER JOIN
STU ON HIS.PID = STU.ID
WHERE (STU.ID = @ID)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-09 : 12:01:30
[code]

SELECT
ID,
HISTORY, ELA, MATH, SCIENCE, FL, VA, Prep,
CASE WHEN HISTORY >= 6 AND
ELA >= 6 AND
MATH >= 6 AND
SCIENCE >= 6 AND
FL >= 6 AND
VA >= 6 AND
Prep >= 6
THEN 'Yes'
ELSE 'No' END AS Yes_No
FROM (
SELECT
STU.ID,
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep
FROM dbo.CRS INNER JOIN
dbo.HIS ON CRS.CN = HIS.CN INNER JOIN
dbo.STU ON HIS.PID = STU.ID
WHERE
STU.ID = @ID
) AS derived

[/code]
Go to Top of Page

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-10 : 15:38:29
Thanks Scott that worked great!...how would one go about to just show yes or no??
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-10 : 16:47:41
quote:
Originally posted by kcarbone1970

Thanks Scott that worked great!...how would one go about to just show yes or no??



Like this?

quote:
Originally posted by ScottPletcher



SELECT
CASE WHEN HISTORY >= 6 AND
ELA >= 6 AND
MATH >= 6 AND
SCIENCE >= 6 AND
FL >= 6 AND
VA >= 6 AND
Prep >= 6
THEN 'Yes'
ELSE 'No' END AS Yes_No
FROM (
SELECT
STU.ID,
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep
FROM dbo.CRS INNER JOIN
dbo.HIS ON CRS.CN = HIS.CN INNER JOIN
dbo.STU ON HIS.PID = STU.ID
WHERE
STU.ID = @ID
) AS derived



Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-12 : 11:03:03
Yep, that should do it.
Go to Top of Page
   

- Advertisement -