Count without nested select: is this possible?

Question

Database:

Department  Position             Points
    A        Manager               50
    A        Supervisor            10
    A        Supervisor            10
    A        Staff                 2
    A        Staff                 2
    B        Manager               40
    B        SuperVisor            8
    B        Staff                 2
    B        Staff                 2
    B        Staff                 2

Desired query result:

Dept  Manager Count  Supervisor Count  Staff Count    Staff Total Pts   

A        1                  2              2                 4
B        1                  1              3                 4

Is the desired query result possible without using nested select with count?

We have a certain stored procedure similar to this using nested counts and we like to make it simpler and perform better/faster

Also you can use PIVOT query for this – Amit Bisht Mar 25 '15 at 07:46 — Amit Bisht, Mar 25 '15 at 07:46

Pரதீப் · Accepted Answer · 2015-03-25T07:47:18.893

0

Use Conditional Aggregate to count only the specific data

Select Department,
       count(case when Position = 'Manager' then 1 END) as Manager,
       count(case when Position = 'Supervisor' then 1 END) as Supervisor,
       count(case when Position = 'Staff' then 1 END) as Staff
From yourtable
Group by Department

If you are using Sql Server use this

SELECT Department,
       Manager,
       Supervisor,
       Staff
FROM   Yourtable
       PIVOT (Count(Position)
             FOR Position IN (Manager,Supervisor,Staff))pv

edited Mar 25 '15 at 07:47

answered Mar 25 '15 at 07:22

Pரதீப்

91,748
19
131
172

Thanks. I added an additional question – Jerome Cylde Mar 26 '15 at 07:03

score 0 · Answer 2 · answered Mar 25 '15 at 07:24

0

Use conditional SUM:

SELECT Department,
       SUM(CASE WHEN Position = 'Manager' THEN 1 END) as Manager,
       SUM(CASE WHEN Position = 'Supervisor' THEN 1 END) as Supervisor,
       SUM(CASE WHEN Position = 'Staff' THEN 1 END) as Staff
FROM yourtable
GROUP BY Department

answered Mar 25 '15 at 07:24

Rigel1121

2,022
1
17
24

Thanks. I added an additional question – Jerome Cylde Mar 26 '15 at 07:03

score 0 · Answer 3 · answered Mar 25 '15 at 07:47

0

Or You can use PIVOT operator:

select Detartment, Manager, Supervisor, Staff
from yourtable
pivot (count(Position) for Position in (Manager, Supervisor, Staff)) Result

answered Mar 25 '15 at 07:47

Gabor Rajczi

471
2
9

Count without nested select: is this possible?

3 Answers3