CODEWITHSUNDEEP
mysqlsqlpivot
user1464296
user1464296

Reputation:

Multi Column cross tabulation AKA pivot

I've been learning and practicing sql for about 6 months. I'm ready to throw in and just admit I'm to dumb for it and my brain just can't wrap around most its patterns. I'm trying to create a pivot table. There isn't much in the way simple literature on the subject and the one source I did find talked of creating cubes and basic entity-attribute-value (EAV) logic.. Whatever that means.

name        action  pagecount
-------------------------------
Company A   PRINT    3
Company A   PRINT    2
Company A   PRINT    3
Company B   EMAIL    6
Company B   PRINT    2
Company B   PRINT    2
Company B   PRINT    1
Company A   PRINT    3

I want to cross tabulate (is that the right term?) column name with the total count of column action and the total count of column pagecount

 name        action_PRINT   action_EMAIL       pagecount_PRINT      pagecount_EMAIL
--------------------------------------------------------------------------------
Company A   4                0                 11                       0
Company B   3                1                 5                        6

Upvotes: 2

Views: 1422

Answers (1)

Taryn
Taryn

Reputation: 247720

This type of operation is known as a PIVOT but MySQL does not have a pivot function so you will need to replicate it using both an aggregate function and a CASE statement.

You can use the following to produce the result:

select name,
  sum(case when action = 'PRINT' then 1 else 0 end) action_print,
  sum(case when action = 'EMAIL' then 1 else 0 end) action_email,
  sum(case when action = 'PRINT' then pagecount else 0 end) pagecount_print,
  sum(case when action = 'EMAIL' then pagecount else 0 end) pagecount_email
from yourtable
group by name

See SQL Fiddle with Demo

The result would be:

|      NAME | ACTION_PRINT | ACTION_EMAIL | PAGECOUNT_PRINT | PAGECOUNT_EMAIL |
-------------------------------------------------------------------------------
| Company A |            4 |            0 |              11 |               0 |
| Company B |            3 |            1 |               5 |               6 |

Upvotes: 2

Related Questions