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 SouthFROM Individuals INNER JOIN EnrollmentsSet ON Individuals.Id = EnrollmentsSet.Enrollments_Individual INNER JOIN Families ON Individuals.Individual_Family = Families.IdGROUP BY EnrollmentsSet.SubProgram_Enrollments, Families.ZipCodeHAVING (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 SouthFROM Individuals INNER JOIN EnrollmentsSet ON Individuals.Id = EnrollmentsSet.Enrollments_Individual INNER JOIN Families ON Individuals.Individual_Family = Families.IdWHERE EnrollmentsSet.SubProgram_Enrollments IN ( 2,3,4)GROUP BY EnrollmentsSet.SubProgram_EnrollmentsORDER BY Families.ZipCode ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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. |
|
|
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" |
|
|
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 SouthFROM Individuals INNER JOIN EnrollmentsSet ON Individuals.Id = EnrollmentsSet.Enrollments_Individual INNER JOIN Families ON Individuals.Individual_Family = Families.IdWHERE (EnrollmentsSet.SubProgram_Enrollments = 2) OR (EnrollmentsSet.SubProgram_Enrollments = 3) OR (EnrollmentsSet.SubProgram_Enrollments = 4)[/code] |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 GrandTotalFROM dbo.Individuals AS iINNER JOIN dbo.EnrollmentsSet AS es ON es.Enrollments_Individual = i.ID INNER JOIN dbo.Families AS f ON f.ID = i.Individual_FamilyWHERE 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" |
|
|
|