CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 609

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

Upvotes: 0

Views: 70

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 4

I_am_Batman
I_am_Batman

Reputation: 915

You could use PIVOT function.

select Funder,
UNITSMAINT+UNITSNOTMAINT as UNITS,
UNITSMAINT,
  UNITSNOTMAINT 
    from
(
SELECT
a.new_funderidname as Funder,b.Maint
FROM new_dealsheet a
LEFT JOIN salesorder B ON a.new_dsheetid = B.salesorderid
WHERE a.New_PassedToAdmin = 1
)
PIVOT 
   (
    count(b.Maint) 
     For(1 as UNITSMAINT,0 as UNITSNOTMAINT)
   );

Refer Pivot function

Upvotes: 0

Me.Name
Me.Name

Reputation: 12544

If maint is always 0 or 1 you could sum them with Sum(b.mainnt) and Sum(1-b.mainnt)

SELECT
a.new_funderidname as Funder,
COUNT(a.new_funderidname) as Units,
Sum(b.mainnt) as UnitsMaint,
Sum(1 - b.mainnt) 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

You might need to do a cast inside Sum if b.mainnt is a bit type: e.g. SUM(cast(b.mainnt as int)) and SUM(1 - cast(b.mainnt as int))

Upvotes: 1

Related Questions