CODEWITHSUNDEEP
sqlmysqldatabasepivotaggregate-functions
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40643

MySQL: Pivot + Counting

I need help with a SQL that will convert this table:

===================
| Id | FK | Status|
===================
| 1  | A  | 100   |
| 2  | A  | 101   |
| 3  | B  | 100   |
| 4  | B  | 101   |
| 5  | C  | 100   |
| 6  | C  | 101   |
| 7  | A  | 102   |
| 8  | A  | 102   |
| 9  | B  | 102   |
| 10 | B  | 102   |
===================

to this:

==========================================
| FK | Count 100 | Count 101 | Count 102 |
==========================================
| A  | 1         | 1         | 2         |
| B  | 1         | 1         | 2         |
| C  | 1         | 1         | 0         |
==========================================

I can so simple counts, etc., but am struggling trying to pivot the table with the information derived. Any help is appreciated.

Upvotes: 3

Views: 4123

Answers (3)

Asgar
Asgar

Reputation: 2422

Just adding a shortcut to @OMG's answer. You can eliminate CASE statement:

SELECT t.fk,
         SUM(t.status = 100) AS count_100,
         SUM(t.status = 101) AS count_101,
         SUM(t.status = 102) AS count_102
    FROM TABLE t
GROUP BY t.fk

Upvotes: 0

bhumi padodara
bhumi padodara

Reputation: 1

use:

select * from 
(select fk,fk  as fk1,statusFK from #t
) as t
pivot
(COUNT(fk1) for statusFK IN ([100],[101],[102])
) AS pt

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332591

Use: 

  SELECT t.fk,
         SUM(CASE WHEN t.status = 100 THEN 1 ELSE 0 END) AS count_100,
         SUM(CASE WHEN t.status = 101 THEN 1 ELSE 0 END) AS count_101,
         SUM(CASE WHEN t.status = 102 THEN 1 ELSE 0 END) AS count_102
    FROM TABLE t
GROUP BY t.fk

Upvotes: 11

Related Questions