Ben Ben - 4 months ago 7
SQL Question

Aggregate function with different conditions

I have this simple query:

SELECT
a.new_funderidname as Funder,
COUNT(a.new_funderidname) as Units
FROM new_dealsheet a
LEFT JOIN salesorder B ON a.new_dsheetid = B.salesorderid
WHERE a.New_PassedToAdmin = 1
GROUP BY a.new_funderidname
ORDER BY Units desc


That's fine, but my B table contains another field called maint, I need to also get the counts for those two possible values, something like

SELECT
a.new_funderidname as Funder,
COUNT(a.new_funderidname) as Units,
COUNT(a.new_funderidname) **WHERE b.maint=1 as UnitsMaint**,
COUNT(a.new_funderidname) **WHERE b.maint=0 as UnitsNotMaint**
FROM new_dealsheet a
LEFT JOIN salesorder B ON a.new_dsheetid = B.salesorderid
WHERE a.New_PassedToAdmin = 1
GROUP BY a.new_funderidname
ORDER BY Units desc


Can I do that within a single query?

Thanks

Answer

Use CASE

SELECT
a.new_funderidname as Funder,
COUNT(a.new_funderidname) as Units,
sum(case when b.maint=1 then 1 else 0 end ) as UnitsMaint,
sum(case when b.maint=0 then 1 else 0 end ) as UnitsNotMaint,
FROM new_dealsheet a
LEFT JOIN salesorder B ON a.new_dsheetid = B.salesorderid
WHERE a.New_PassedToAdmin = 1
GROUP BY a.new_funderidname 
ORDER BY Units desc
Comments