Group By and Counting inside group

Question

I have a scenario in which I want to count records after grouping the data. (Actual scenario contains multiple tables.)

create table tblXYZ
(
id int,
Age int,
Typ char
)

Table contains this data:

| ID | Age  | Typ
------------------
| 1  | 20   | A
| 2  | 20   | A
| 3  | 21   | B
| 4  | 22   | B
| 5  | 22   | A
| 6  | 23   | B
| 7  | 23   | A
| 8  | 23   | A
| 9  | 25   | B
| 10 | 25   | B
| 11 | 25   | A

If I apply a group by Age, I'll get:

| Age  | Typ_Count
-------------
| 20   | 2
| 21   | 1
| 22   | 2
| 23   | 3
| 25   | 3

But I want to get the typ_count based on every specific typ for every age group.

Desired result:

    | Age  | Typ_A_Count | Typ_B_Count
    ---------------------------------
    | 20   | 2           | 0
    | 21   | 0           | 1
    | 22   | 1           | 1
    | 23   | 2           | 1
    | 25   | 1           | 2

Thanks.

You could use a pivot if the types are static or a dynamic pivot to achieve this — Mihail Stancescu, Jan 20 '16 at 14:22
Do they have to be in different columns? can't you just group by age and typ? If they have to be in columns use a case statement for each type. If you have an random number of types, then you need to write a dynamic Piviot such as: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query. Too many unknown factors to make a "quality" recommendation at this time. — xQbert, Jan 20 '16 at 14:24

score 8 · Accepted Answer · answered Jan 20 '16 at 14:31

8

You can count multiple categories like this:

SELECT
    Age,
    COUNT(CASE WHEN Typ = 'A' THEN 1 ELSE NULL END) AS CountTypA,
    COUNT(CASE WHEN Typ = 'B' THEN 1 ELSE NULL END) AS CountTypB
FROM tblXYZ
GROUP BY Age

answered Jan 20 '16 at 14:31

morgb

2,252
2
14
14

score 5 · Answer 2 · answered Jan 20 '16 at 14:28

5

SELECT Age,Typ
FROM tblXYZ
PIVOT
(
 count (Typ) FOR Typ IN ('A','B')
)

answered Jan 20 '16 at 14:28

Mihai-Daniel Virna

984
9
19

Group By and Counting inside group

2 Answers2