October 15, 2010 at 2:02 pm
select p.ProjectName, b.BidPackageNumber, c.ConstructionManager,
Case when (cp.City = 'Cleveland' or cp.City = 'Cleve')
then CASE when cp.Race = 'Black' then sum(cp.WorkHourPerWeek)
end
else 0
end as Black_Hours,
CASE when cp.Race = 'White' then sum(cp.WorkHourPerWeek)
END as White_Hours,
CASE
when cp.Race = 'Asian' then SUM(cp.WorkHourPerWeek)
END as Asian_Hours,
CASE
when cp.Race = 'NativeIndian' then SUM(cp.WorkHourPerWeek)
END As Native_Indian_Hours,
CASE
when cp.Race = 'Hispanic' then SUM(cp.WorkHourPerWeek)
END as Hispanic_Hours,
CASE
when cp.Race = 'Other' then SUM(cp.WorkHourPerWeek)
end as Other_Hours
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, cp.City
My results
ProjectName BidPackageNumber ConstructionManager Black_Hours White_Hours Asian_Hours Native_Indian_Hours Hispanic_Hours Other_Hours
Medical Center BP01A Building Company 80 Null null
Center Parking BP-A Building Company 0 8 null null
Parking Garage BP-A Building Company NULL 16
which works fine. Now I need to total the sum number of each group black group, white group, asian group, native indian group, hispanic group add only the group of people that is located in cleveland together and display as cleveland residency.
any help would be wonderful.
October 15, 2010 at 4:05 pm
Did you try the WITH ROLLUP operator?
If that doesn't help you, please provide table def and some sample data together wit your expected result so we have something to test against. See the first link in my signature for details.
Also, please confirm that this is a real business case (sounds like homework...)
October 16, 2010 at 7:27 pm
what do you need to confirm a real business.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply