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)
 Every Zip Counts

Author  Topic 

wdarnellg
Starting Member

14 Posts

Posted - 2013-07-12 : 06:34:32
Hello. I need help with using SQL in SQL SERVER 2012 to count the number of people in a certain Zip Code and total them by North and South. My current query gives me 19 rows, and I can't figure how to sum them so that there are only two rows.
Thanks for any help.


SELECT COUNT(CASE WHEN (Families.ZipCode = '75229') OR
(Families.ZipCode = '75231') THEN Families.ZipCode END) AS North, COUNT(CASE WHEN (Families.ZipCode = '75115') OR
(Families.ZipCode = '75210') OR
(Families.ZipCode = '75224') OR
(Families.ZipCode = '75224') THEN Families.ZipCode END) AS South
FROM Individuals INNER JOIN
EnrollmentsSet ON Individuals.Id = EnrollmentsSet.Enrollments_Individual INNER JOIN
Families ON Individuals.Individual_Family = Families.Id
GROUP BY EnrollmentsSet.SubProgram_Enrollments, Families.ZipCode
HAVING (EnrollmentsSet.SubProgram_Enrollments = 2) OR
(EnrollmentsSet.SubProgram_Enrollments = 3) OR
(EnrollmentsSet.SubProgram_Enrollments = 4)
ORDER BY Families.ZipCode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:42:42
DO you mean this?

SELECT SUM(CASE WHEN Families.ZipCode IN( '75229','75231') THEN 1 ELSE 0 END) AS North,
SUM(CASE WHEN Families.ZipCode IN ('75115','75210','75224','75224') THEN 1 ELSE 0 END) AS South
FROM Individuals INNER JOIN
EnrollmentsSet ON Individuals.Id = EnrollmentsSet.Enrollments_Individual INNER JOIN
Families ON Individuals.Individual_Family = Families.Id
WHERE EnrollmentsSet.SubProgram_Enrollments IN ( 2,3,4)
GROUP BY EnrollmentsSet.SubProgram_Enrollments
ORDER BY Families.ZipCode


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

wdarnellg
Starting Member

14 Posts

Posted - 2013-07-12 : 13:23:30
Sorry, I meant two columns totaling the count for each zip code in each North or South location. There should be just one row.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-12 : 13:48:22
Can you show what you are getting and what your expected output looks like.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-12 : 14:33:56
Comment out the GROUP BY clause?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-07-12 : 17:20:00
[code]
SELECT COUNT(CASE WHEN (Families.ZipCode = '75229') OR
(Families.ZipCode = '75231') THEN Families.ZipCode END) AS North,
COUNT(CASE WHEN (Families.ZipCode = '75115') OR
(Families.ZipCode = '75210') OR
(Families.ZipCode = '75224') OR
(Families.ZipCode = '75224') THEN Families.ZipCode END) AS South
FROM Individuals INNER JOIN
EnrollmentsSet ON Individuals.Id = EnrollmentsSet.Enrollments_Individual INNER JOIN
Families ON Individuals.Individual_Family = Families.Id
WHERE (EnrollmentsSet.SubProgram_Enrollments = 2) OR
(EnrollmentsSet.SubProgram_Enrollments = 3) OR
(EnrollmentsSet.SubProgram_Enrollments = 4)
[/code]
Go to Top of Page

wdarnellg
Starting Member

14 Posts

Posted - 2013-07-12 : 23:10:45
Thank you Visakh16. When I follow SwePeso's suggestion on commenting out the Group By, I get the desired result. I also like ScotPletcher's solution. Both approaches give me the needed numbers. Thank you all!!

wdg
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-13 : 11:38:54
quote:
Originally posted by wdarnellg

Thank you Visakh16. When I follow SwePeso's suggestion on commenting out the Group By, I get the desired result. I also like ScotPletcher's solution. Both approaches give me the needed numbers. Thank you all!!

wdg



Ok..So you wanted grand totals. cool that you got it sorted out

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-14 : 01:39:37
[code]SELECT SUM(CASE WHEN f.ZipCode IN ('75229', '75231') THEN 1 ELSE 0 END) AS North,
SUM(CASE WHEN f.ZipCode IN ('75115', '75210', '75224', '75224') THEN 1 ELSE 0 END) AS South,
SUM(1) AS GrandTotal
FROM dbo.Individuals AS i
INNER JOIN dbo.EnrollmentsSet AS es ON es.Enrollments_Individual = i.ID
INNER JOIN dbo.Families AS f ON f.ID = i.Individual_Family
WHERE es.SubProgram_Enrollments IN ( 2,3,4)
AND f.ZipCode IN ('75229', '75231', '75115', '75210', '75224', '75224')
ORDER BY f.ZipCode[/code]


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -