CODEWITHSUNDEEP
sqlsql-server
TyForHelpDude
TyForHelpDude

Reputation: 5001

Find department counts using group by query

I need to find counts of buildings exist in a department so I use the query below 

  select sil.Name, Count((select * from Common.Building where TypeCode=10012)), Count((select * from Common.Building where TypeCode=98)) from Common.Building ok
  join Skrs.Department sil on ok.DepartmentId=sil.Id
  group by sil.Name

here is the sql error msg:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

how can I retrieve the data?

EDITED: sample data:

Common.Buildings 

DepartmentID    Name
35              Kurum Alt Birimleri
35              VEREM SAVAŞ DİSPANSERİ
6               DİĞER
5               SAĞLIK OCAĞI

Skrs.Department

Id  Adi
1   ADANA
2   ADIYAMAN
3   AFYONKARAHİSAR
4   AĞRI
5   AMASYA

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I am guessing that you want something like this:

select sil.Name,
       sum(case when b.TypeCode = 10012 then 1 else 0 end),
       sum(case when b.TypeCode = 98 then 1 else 0 end)
from Common.Building b join
     Skrs.Department sil
     on b.DepartmentId = sil.Id
group by sil.Name;

Upvotes: 2

Related Questions