question

postonoh avatar image
postonoh asked

Case math problem

I have this select p.ProjectName, b.BidPackageNumber, c.ConstructionManager, CASE when cp.Race = 'Black' then sum(cp.WorkHourPerWeek) end as BlackHours, CASE when cp.Race = 'White' then sum(cp.WorkHourPerWeek) END as WhiteHours, CASE when cp.Race = 'Asian' then SUM(cp.WorkHourPerWeek) END as AsianHours, CASE when cp.Race = 'NativeIndian' then SUM(cp.WorkHourPerWeek) END As NativeIndianHours, CASE when cp.Race = 'Hispanic' then SUM(cp.WorkHourPerWeek) END as HispanicHours, CASE when cp.Race = 'Other' then SUM(cp.WorkHourPerWeek) end as otherHours FROM CertifiedPayroll cp inner join BidPackage b on b.BidPackage_ID = cp.BidPackage_ID inner join Contractors c on c.Trade_ID = cp.SubContractor_ID inner join Projects p on p.Project_ID = b.Project_ID group by p.ProjectName, b.BidPackageNumber, cp.Race, c.ConstructionManager Now I need for when every Cleveland or Cleve are found in the city column it multiplies by race If there a better way please let me.
t-sqlhomework
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please explain what you mean about "it multiplies by race". I am guessing that City is a column in the Projects table, right?
0 Likes 0 ·
Do people (and databases) really still use the term "race"? Anyway - Can you provide some data examples, to show what you have and what you want as a result.
0 Likes 0 ·
I have to total race. Then total city only if they live in Cleveland. city is in a table called Certified Payroll with the rest of the columns (cp.Race) what I need is for when Cleveland or Cleve is entered. something like this if White and lives in Cleveland or Cleve add sum hours of the race that have Cleveland chosen to display as Cleveland Total. so after it sum the total of each race everyone then check for Cleveland resident then add the sum of Cleveland resident display as Cleveland Resident Total. I hope I am explaining right. This is for a report. I doing this as a view
0 Likes 0 ·
Would it not make sense to then add the column to the group and keep the sum as it is?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Do you mean that in addition to breaking it down by race you also need to filter by Cleve or Cleveland? Wouldn't that simply go into the WHERE clause? Or do you mean that you will only break down the hours by race when you have Cleve or Cleveland? If so, you could, I'm not crazy about it, but you could, nest case statements. SELECT ... CASE WHEN (cp.City = 'Cleveland' or cp.City = 'Cleve') THEN CASE when cp.Race = 'Black' then sum(cp.WorkHourPerWeek) end ELSE 42 END as BlackHours But, that's could look funny as a result set.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for `ELSE 42`!
3 Likes 3 ·
@!postonoh Then... if I understand what you're saying, you want to break out Cleveland from the rest of the data completely... I'd do that as a sub-select, possibly a CTE. I'm still not sure I understand though.
1 Like 1 ·
In the last census, I put down my religion as 'Jedi' - does that mean I'm going to get paid for 42 hour weeks irrespective now? :)
0 Likes 0 ·
That is a religion and not a race - young padawan!
0 Likes 0 ·
I have to total race. Then total city only if they live in Cleveland. something like this if White and lives in Cleveland or Cleve add sum hours of the race that have Cleveland chosen to display as Cleveland Total. so after it sum the total of each race everyone then check for Cleveland resident then add the sum of Cleveland resident display as Cleveland Resident Total. I hope I am explaining right. This is for a report. I doing this as a view
0 Likes 0 ·
philnolan avatar image
philnolan answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.