SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 adding case to a multiple count statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Starting Member

USA
35 Posts

Posted - 08/09/2013 :  02:25:19  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 08/09/2013 :  06:02:43  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
421 Posts

Posted - 08/09/2013 :  12:01:30  Show Profile  Reply with Quote


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

Go to Top of Page

kcarbone1970
Starting Member

USA
35 Posts

Posted - 08/10/2013 :  15:38:29  Show Profile  Reply with Quote
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

547 Posts

Posted - 08/10/2013 :  16:47:41  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
421 Posts

Posted - 08/12/2013 :  11:03:03  Show Profile  Reply with Quote
Yep, that should do it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000