CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
m0ngr31
m0ngr31

Reputation: 830

Getting the sum of a count with conditions

So I am trying to get a sum of a count that has conditions (different from the rest of the query)

This is what I have so far:

SELECT studemo.ssid                                 AS [SSID], 
       stustat.graden                               AS Grade, 
       studemo.lastname                             AS [Last Name], 
       studemo.firstname                            AS [First Name], 
       (SELECT Count(*) 
        WHERE  attend.attendc IN ( 'E', 'G', 'H', 'I', 
                                   'J', 'L', 'M', 'P', 
                                   'Q', 'V', 'X' )) AS [Days Absent], 
       attend.attendc                               AS [Attendance Code], 
       attend.ddate                                 AS [Date] 
FROM   track, 
       stustat, 
       stusched, 
       studemo, 
       attend 
WHERE  studemo.suniq = stustat.suniq 
       AND attend.scduniq = stusched.scduniq 
       AND studemo.suniq = stusched.suniq 
       AND stustat.trkuniq = track.trkuniq 
       AND track.schoolc = '408' 
       AND track.schyear = '2013' 
       AND stustat.edate >= '08/21/2012' 
       AND stustat.xdate IS NULL 
ORDER  BY [last name], 
          [first name], 
          attend.ddate

This will get me the right info, just not in the way I'm trying to get it. It will give me a row for every attendance code. What I'm hoping to do is just add up the number of times I get something from the count(*) subquery. I'm tried sum() in about 100 different ways, but I can't get it. Any suggestions?

Upvotes: 0

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Try replacing the line:

(select count(*) where attend.attendc IN ('E','G','H','I','J','L','M','P','Q','V','X')) AS [Days Absent],

With the window function:

sum(case when attend.attendc IN ('E','G','H','I','J','L','M','P','Q','V','X') then 1 else 0 end) over () as [Days Absent]

That is the total number of days absent. I'm guessing that you want this by SSID. In that case, use partition by:

sum(case when attend.attendc IN ('E','G','H','I','J','L','M','P','Q','V','X') then 1 else 0 end)
          over (partition by SSID) as [Days Absent]

Upvotes: 3

Related Questions