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)
 Every Zip Counts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wdarnellg
Starting Member

14 Posts

Posted - 07/12/2013 :  06:34:32  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/12/2013 :  06:42:42  Show Profile  Reply with Quote
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 - 07/12/2013 :  13:23:30  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/12/2013 :  13:48:22  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 07/12/2013 :  14:33:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Comment out the GROUP BY clause?



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

ScottPletcher
Constraint Violating Yak Guru

USA
425 Posts

Posted - 07/12/2013 :  17:20:00  Show Profile  Reply with Quote

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)

Edited by - ScottPletcher on 07/12/2013 17:20:26
Go to Top of Page

wdarnellg
Starting Member

14 Posts

Posted - 07/12/2013 :  23:10:45  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/13/2013 :  11:38:54  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 07/14/2013 :  01:39:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



N 56°04'39.26"
E 12°55'05.63"
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.09 seconds. Powered By: Snitz Forums 2000