Deaggrigate then apply With Rollup

Question

need just a bit more tweaking to my query. I have a table that is filled with assignments and values such as this:

a_inv
assignment_id    total
=========================
1                500

Then two tables that are related, categories and channels for the assignment, for example:

a_cat
assignment_id    category_id
==============================
1                1
1                11

a_ch
assignment_id    channel_id
==============================
1                16
1                25

So what I need to do is figure out how much was spent per category and channel (with rollup).

Here's the query that I got the closest with:

SELECT 
    ac.category_id, ach.channel_id, ((ia.total/COUNT(DISTINCT ac2.category_id))/COUNT(DISTINCT ach2.channel_id)) AS cur_total
FROM 
    a_cat ac
LEFT JOIN 
    a_ch ach ON ach.assignment_id = ac.assignment_id
LEFT JOIN 
    a_inv ia ON ia.assignment_id = ac.assignment_id
LEFT JOIN
    a_cat ac2 ON ach.assignment_id = ac2.assignment_id
LEFT JOIN 
    a_ch ach2 ON ach2.assignment_id = ac.assignment_id
WHERE
    ac.assignment_id = 1
GROUP BY
    ac.category_id, ach.channel_id WITH ROLLUP

It gives me a result that is very close to what i need:

category_id   channel_id   cur_total
=====================================
1             16           125.0000
1             25           125.0000
1             NULL         125.0000     <---- this should be "250.0000"
11            16           125.0000
11            25           125.0000
11            NULL         125.0000     <---- this should be "250.0000"
NULL          NULL         125.0000     <---- this should be "500.0000"

Any help is greatly appreciated.

Thanks!

Answer 1

Try this

SELECT category_id, channel_id, SUM(cur_total) 
FROM (
    SELECT 
        ac.category_id, ach.channel_id, ((ia.total/COUNT(DISTINCT ac2.category_id))/COUNT(DISTINCT ach2.channel_id)) AS cur_total
    FROM 
        a_cat ac
    LEFT JOIN 
        a_ch ach ON ach.assignment_id = ac.assignment_id
    LEFT JOIN 
        a_inv ia ON ia.assignment_id = ac.assignment_id
    LEFT JOIN
        a_cat ac2 ON ach.assignment_id = ac2.assignment_id
    LEFT JOIN 
        a_ch ach2 ON ach2.assignment_id = ac.assignment_id
    WHERE
        ac.assignment_id = 1
    GROUP BY
        ac.category_id, ach.channel_id
    ) as t1
GROUP BY category_id, channel_id WITH ROLLUP