mysql - Adding a pivot to a rollup

Question

I have the following query that gives me aggregated data that I would like to pivot:

select provider, title, hd_or_sd, SUM(customer_price), count(distinct title) 
from `100` group by provider, title, hd_or_sd with rollup

I would like to pivot on the sd_or_hd column, so that my result looks like:

                                      hd              sd          total   
provider         title             rev. count      rev. count   rev. count
DISTRIBBER                                         16.99  1     16.99  1
DISTRIBBER       finding Joe                       16.99  1     16.99  1

How would I do this query? Also, I have noticed that the Rollup is not perfect -- for example, with Electric entertainment, it shows 5 titles but has the hd_or_sd value "HD", even though several of those titles are SD. Why does this occur?

Here is a sample of the data: http://sqlfiddle.com/#!9/a9b5d9/1. And here is what the final result should be (minus the strange number formatting) --

Note that I'd also like to accomplish this without using CASE statements as the pivoted column could potentially have many different values.

Answer 1

You need to use SUM with CASE Operator, like this:

select provider, title, 
sum(case when hd_or_sd = 'HD' then 1 else 0 end) as HD,
sum(case when hd_or_sd = 'SD' then 1 else 0 end) as SD,
sum(case when hd_or_sd = 'HD' then customer_price else 0 end) as HD_price,
sum(case when hd_or_sd = 'SD' then customer_price else 0 end) as SD_price,
sum(customer_price) revenue from `100`
group by provider, title
with rollup

Result:

|               provider |                      title | HD | SD | HD_price | SD_price | revenue |
|------------------------|----------------------------|----|----|----------|----------|---------|
|             DISTRIBBER |                Finding Joe |  0 |  1 |        0 |    16.99 |   16.99 |
|             DISTRIBBER |                     (null) |  0 |  1 |        0 |    16.99 |   16.99 |
|            Echo Bridge |               Do Something |  0 |  1 |        0 |     1.99 |    1.99 |
|            Echo Bridge |                 Down in LA |  2 |  2 |        0 |        0 |       0 |
|            Echo Bridge | The L.A. Complex, Season 1 |  0 |  1 |        0 |    19.99 |   19.99 |
|            Echo Bridge | The Other Side of the Door |  1 |  2 |     2.99 |     3.98 |    6.97 |
|            Echo Bridge |               Who You Know |  0 |  2 |        0 |     3.98 |    3.98 |
|            Echo Bridge |                     (null) |  3 |  8 |     2.99 |    29.94 |   32.93 |
| Electric Entertainment |         Leverage, Season 4 |  0 |  1 |        0 |    31.99 |   31.99 |
| Electric Entertainment |     The Cross My Heart Job |  1 |  0 |     2.99 |        0 |    2.99 |
| Electric Entertainment |             The Inside Job |  0 |  1 |        0 |     1.99 |    1.99 |
| Electric Entertainment |              The Radio Job |  0 |  1 |        0 |     1.99 |    1.99 |
| Electric Entertainment |       The Scheherazade Job |  1 |  0 |     2.99 |        0 |    2.99 |
| Electric Entertainment |                     (null) |  2 |  3 |     5.98 |    35.97 |   41.95 |
|               HALLMARK |      The Good Witch's Gift |  0 |  1 |        0 |     3.99 |    3.99 |
|               HALLMARK |                     (null) |  0 |  1 |        0 |     3.99 |    3.99 |
|            Quebec Inc. |        2 Frogs In the West |  1 |  0 |     5.99 |        0 |    5.99 |
|            Quebec Inc. |                     (null) |  1 |  0 |     5.99 |        0 |    5.99 |
|                 VIRGIL |         One Lucky Elephant |  0 |  1 |        0 |     3.99 |    3.99 |
|                 VIRGIL |                     (null) |  0 |  1 |        0 |     3.99 |    3.99 |
|                 (null) |                     (null) |  6 | 14 |    14.96 |    90.88 |  105.84 |

SQL Fiddle

Alternatively, you can just simply ROLLUP by hd_or_sd and have the PIVOT done in the application, like this:

select provider, title, hd_or_sd,
sum(customer_price) revenue from `100`
group by provider, hd_or_sd, title
with rollup