Ok, here's my problem. I need something like a grand total from a summary field that looks like this:Asian 0 0 0 0 9 22 18 9 10 0 68Black 0 0 2 0 14 12 11 0 4 7 50Hispan 0 0 0 0 12 5 5 6 0 0 28Native 0 0 0 0 0 2 3 3 0 0 8N/A 0 0 0 0 0 3 2 0 0 0 5Pacific 1 0 0 0 3 0 0 2 0 0 6White 0 0 4 0 16 21 17 9 9 8 84
The code to generate this looks like this:SELECT vd.Ethnicity,SUM(CASE WHEN vd.Age >= 16 AND vd.Age <= 18 AND vd.Gender = 'Male' THEN 1 ELSE 0 END) AS Mage1,SUM(CASE WHEN vd.Age >= 16 AND vd.Age <= 18 AND vd.Gender = 'Female' THEN 1 ELSE 0 END) AS Fage1,SUM(CASE WHEN vd.Age >= 19 AND vd.Age <= 24 AND vd.Gender = 'Male' THEN 1 ELSE 0 END) AS Mage2,SUM(CASE WHEN vd.Age >= 19 AND vd.Age <= 24 AND vd.Gender = 'Female' THEN 1 ELSE 0 END) AS Fage2,--rest of them look the same--Will sum along the rowsSUM(CASE WHEN (vd.Age >= 16) AND (vd.Gender = 'Female' OR vd.Gender = 'Male') THEN 1 ELSE 0 END) AS TotalAgeFROM ViewDemographics vdGROUP BY vd.Ethnicity
Now, I can't figure out how to get a total per column and a grand total......Little help.--Nick
SUM a SUM
Posted - 2006-10-12 : 21:43:02
