select sum statement

Question

I created this select statement I will convert to a view. I need help with this. I need to be able to add the total of Minority that = Yes and No show total on report pages.

select
    ps.BidPackage_ID,
    ps.Project_ID,
    SUM (case ps.Minority when 'Yes' then 1 else 0 end) MinorityTotal,
    SUM (case ps.Gender when 'Female' then 1 else 0 end) FemaleTotal,
    SUM(case ps.Cleveland_Resident when 1 then 1 else 0 end) ClevelandResidents,
    ps.SubContractor
from
    PersonnelSummary ps
group by
    ps.BidPackage_ID,
    ps.Project_ID,
    ps.SubContractor

Answer 1

You nearly have it:

...
SUM (case ps.Minority when 'Yes' then 1 else 0 end) AS MinorityYes,
SUM (case ps.Minority when 'No' then 1 else 0 end) AS MinorityNo,
COUNT(*) AS Total,
...

With the Total I'm assuming that every row should be counted. This is what you want if:

• The only values that exist in the column are 'Yes' and 'No' or
• Values different from 'Yes' and 'No' should also be counted in the total.

Answer 2

You're forcing us to guess what you want. You have a count of the people who said that they were in a minority; do you want a count of the people who said No? Or do you want a count of the number who said 'either "Yes" or "No"' and excluding those who gave 'decline to say' or simply no answer at all?

select
    ps.BidPackage_ID,
    ps.Project_ID,
    SUM (case ps.Minority when 'Yes' then 1 else 0 end) MinorityTotalYes,
    SUM (case ps.Minority when 'No'  then 1 else 0 end) MinorityTotalNo,
    SUM (case ps.Minority when 'Yes' then 1 when 'No' then 1 else 0 end) 
    AS StatedMinorityTotal,
    SUM (case ps.Gender when 'Female' then 1 else 0 end) FemaleTotal,
    SUM(case ps.Cleveland_Resident when 1 then 1 else 0 end) ClevelandResidents,
    ps.SubContractor
from
    PersonnelSummary ps
group by
    ps.BidPackage_ID,
    ps.Project_ID,
    ps.SubContractor