CODEWITHSUNDEEP
sqlsql-serversql-server-2012count
danrichsol
danrichsol

Reputation: 1

Multiple counts on one column in SQL Server

I have to solve the following task in SQL Server: I have to count different times on one column from the following two tables.

I need the number of users (not the number of bookings) of the different teams booking on the projects in a specific time period.

With Count distinct I can count the All_Users column. But how to count the rest?

Can somebody give me a hint?

Table Project:

Project  Date          User
-----------------------------
P1       01.02.2017    John
P1       01.02.2017    Mary
P1       01.02.2017    Molly
P2       01.02.2017    John
P2       02.02.2017    Paul
P2       05.02.2017    Bill
P2       06.02.2017    Emily
P3       03.04.2017    Steve
P3       04.04.2017    Steve
P3       05.04.2017    John

Table User:

User    Team
-------------
John    EXT
Emily   BI
Steve   BC
Bill    NT
Mary    EXT
Molly   BC
Paul    NT

Table Result:

Projekt ALL_USERS   TEAM_BI-BC-NT   TEAM_BI TEAM_BC TEAM_NT TEAM_EXT
--------------------------------------------------------------------
P1         3              1             0       0      0       2
P2         4              3             1       0      2       1
P3         2              1             0       1      0       1

Upvotes: 0

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Use conditional aggregation:

select p.projekt, count(*) as all_users, 
       sum(case when team in ('BI', 'BC', 'NT') then 1 else 0 end) as bi_bc_nt_cnt,
       sum(case when team = 'BI' then 1 else 0 end) as bi_cnt,
       sum(case when team = 'BC' then 1 else 0 end) as bc_cnt,
       sum(case when team = 'NT' then 1 else 0 end) as nt_cnt,
       sum(case when team = 'EXT' then 1 else 0 end) as ext_cnt
from project p join
     user u
     on p.user = u.user
group by p.projekt;

Based on your comment, you need some sort of distinct. Let me stick with the same structure and do this in a subquery:

select p.projekt, count(*) as all_users, 
       sum(case when u.team in ('BI', 'BC', 'NT') then 1 else 0 end) as bi_bc_nt_cnt,
       sum(case when u.team = 'BI' then 1 else 0 end) as bi_cnt,
       sum(case when u.team = 'BC' then 1 else 0 end) as bc_cnt,
       sum(case when u.team = 'NT' then 1 else 0 end) as nt_cnt,
       sum(case when u.team = 'EXT' then 1 else 0 end) as ext_cnt
from (select distinct projekt, user
      from project p
     ) p join
     user u
     on p.user = u.user
group by p.projekt;

Upvotes: 1

Related Questions