CODEWITHSUNDEEP
sqlms-access-2013
Mark Wright
Mark Wright

Reputation: 1

Multiple counts based on column values

Can some one help with some Access SQL ?

If I have the data 

Grade of Ulcer  Source of Pressure Ulcer    
Grade 4         Admitted    
Grade 4         Admitted    
Grade 3         Admitted    
Grade 3         Admitted    
Grade 4         Admitted    
Grade 4         Admitted    
Grade 3         Acquired

How can I return in one query 

                     Acquired          Admitted    
Grade 3              1                 2    
Grade 4              0                 4

I can do this is 2 queries but not one

Here is one example

SELECT 2015_pressur.[Grade of Ulcer], Count(2015_pressur.[Grade of Ulcer]) AS AcquiredCount    
FROM 2015_pressur
WHERE (((2015_pressur.[Source Of Pressure Ulcer])='Acquired'))    
GROUP BY 2015_pressur.[Grade of Ulcer];

Upvotes: 0

Views: 38

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

You can do it like this:

select
  T.[Grade of Ulcer], 
  sum(IIF(T.[Source Of Pressure Ulcer] = 'Acquired', 1, 0)) AS AcquiredCount,
  sum(IIF(T.[Source Of Pressure Ulcer] = 'Admitted', 1, 0)) AS AdmittedCount    
from 2015_pressur as T
where T.[Source Of Pressure Ulcer] in ('Acquired', 'Admitted')
group by  T.[Grade of Ulcer]

Upvotes: 1

ughai
ughai

Reputation: 9880

You can use SWITCH with GROUP BY like this.

SELECT
    2015_pressur.[Grade of Ulcer],
    SUM(SWITCH(2015_pressur.[Source Of Pressure Ulcer] = 'Acquired',1,true,0)) Acquired,
    SUM(SWITCH(2015_pressur.[Source Of Pressure Ulcer] = 'Admitted',1,true,0)) Admitted
FROM 2015_pressur
GROUP BY 2015_pressur.[Grade of Ulcer]

Upvotes: 1

Related Questions