Help with Select Query 1

[ecugrad]

Not sure if I have a clue what I'm doing. But, I know it's not working. I'm trying to pull quarterly data from one table. Summing up month 1,2, and 3 as Qtr 1 and 3,4 and 5 as Qtr2 and group by lastName:

Table:
LastName
Firstname
Queuescore
Month ex; 1,2,3, etc


SELECT SUM(QueueScore) AS QueueScoreQtr1, LastName
FROM dbo.CapacityMonthlyTotals
WHERE ([Month] = 1) OR
([Month] = 2) OR
([Month] = 3)

(SELECT SUM(QueueScore) AS QueueScoreQtr2, LastName
FROM dbo.CapacityMonthlyTotals
WHERE ([Month] = 4) OR
([Month] = 5) OR
([Month] = 6)
)
GROUP BY LastName

Thanks

 

[gmmastros]

Try this...

SELECT SUM(Case When [Month] In (1,2,3) Then QueueScore Else 0 End) AS QueueScoreQtr1, 
       SUM(Case When [Month] In (4,5,6) Then QueueScore Else 0 End) AS QueueScoreQtr2,
       LastName
FROM   dbo.CapacityMonthlyTotals
Group By LastName

If this works for you, and you'd like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

If I understand you correctly you want to SUM QueueScore for each Name quarterly, is that right?
If so try:

SELECT LastName, FirstName,
       SUM (CASE WHEN [Month] < 4
                 THEN QueueScore
            ELSE 0 END) AS Qtr1,
       SUM (CASE WHEN [Month] BETWEEN 4 AND 6
                 THEN QueueScore
            ELSE 0 END) AS Qtr2
FROM  dbo.CapacityMonthlyTotals
GROUP BY LastName, FirstName

You could remove FirstName from the query if you didn't needed to.

If you need this to be not as Flat result but to have a records for each quarter separated:

[code]
SELECT LastName, FirstName,
       CAST(([Month]+2)/3 as int) AS Qt,
       SUM (QueueScore) AS QueueScore
FROM  dbo.CapacityMonthlyTotals
GROUP BY LastName, FirstName, CAST(([Month]+2)/3 as int)

(not tested)



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP