CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Sebastian
Sebastian

Reputation: 33

Group by and count apperances in other column

My table look like this:

----------------------------------------------
|id    | action   |  building   | date       |
----------------------------------------------
|1     | IN       | 1000        | 01-01-2015 |
|2     | OUT      | 1000        | 01-01-2015 |
|3     | OUT      | 1000        | 05-01-2015 |
|4     | IN       | 2000        | 01-01-2015 |
----------------------------------------------

I would like to group the result by building and count the how many IN and OUT actions exists. Data and id doesn't matter in the result. The result should be like:

-------------------------
| Building   | IN | OUT |
-------------------------
| 1000       | 1  | 2   |
| 2000       | 1  | 0   |
-------------------------

The action column can only contain IN and OUT.

My best attempt is:

select distinct (action), building, count(*)
from table
group by action, building

Output:

-------------------------------------
| action     | Building  | count(*) |
-------------------------------------
| IN         | 1000      | 1        |
| OUT        | 1000      | 2        |
| IN         | 2000      | 1        |
-------------------------------------

Upvotes: 0

Views: 32

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You need to use conditional aggregation:

select building, 
       count(CASE WHEN action = 'IN' THEN 1 END) AS 'IN',
       count(CASE WHEN action = 'OUT' THEN 1 END) AS 'OUT'
from table
group by building

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Do it with conditional aggregation:

select Building,
       sum(case when action = 'IN' then 1 else 0 end) as [IN],
       sum(case when action = 'OUT' then 1 else 0 end) as [OUT],
from TableName
group by Building

Upvotes: 2

Related Questions