SQL group by and aggregation in two columns

Question

This is a follow up on my previous question, kindly answered by @Barmar: When using MYSQL GROUP BY WITH ROLLUP on Year(date) and Month(date) i am not able to change Null to 'Total'

I have a SQL query which gets data from my transaction, subkategori, and kategori tables and this works fine. But now I want to add budget table and sum the budgeted amount. My approach is as below:

$stmt = $dbh->prepare("
                SELECT IFNULL(Year, 'All') AS Year,
                       IFNULL(Month, 'All') AS Month,
                       IFNULL(Kategori,'All') AS Kategori,
                       IFNULL(Subkategori,'All') AS Subkategori,
                       Belopp,
                       Budget
                FROM (
                      SELECT YEAR(transaktioner.datum) AS Year,
                             MONTH(transaktioner.datum) AS Month,
                             kategori.kat_namn AS Kategori,
                             subkategori.subkat_namn AS Subkategori,
                             SUM(transaktioner.belopp)  AS Belopp,
                             SUM(budget.budgetedAmmount) AS Budget

                      FROM subkategori
                      LEFT JOIN transaktioner 
                            ON subkategori.subkat_id=transaktioner.subkat_id
                      LEFT JOIN kategori
                            ON subkategori.kat_id = kategori.kat_id
                      LEFT JOIN budget
                            ON subkategori.subkat_id = budget.subkat_id

                      GROUP BY Year, Month, Kategori, Subkategori
                      WITH ROLLUP

                    ) AS x


                    WHERE Year =".$year." 

                    ");

It gives me a null output. Can somebody help me with this one?

Tables are as follows:

transaktioner

id
datum
beskrivning
overforing
belopp
balans
subkat_id
konto_id
latest_update

kategori

kat_id
kat_namn
type
latest_update

subkategori

subkat_id
subkat_namn
kat_id
latest_update

Budget table

budget_id
subkat_id
budgetedAmmount
user_id
year

DESIRED Result table

Year    Month   Kategory    Subkategory     Spent   Budget
2017    1       Kat1        Subkat1         1000    500
2017    1       Kat1        Subkat2         500     250
2017    1       kat1        ALL             1500    750
2017    1       Kat2        Subkat1         2000    1000
2017    1       Kat2        Subkat2         450     500
2017    1       kat2        ALL             2450    1500
2017    1        ALL        ALL             3950    2250
ALL     ALL      ALL        ALL             3950    2250

See fiddle: http://sqlfiddle.com/#!9/35a778/2

"it doesn't work"... a classic comment, but it has no meaning. Please be more precise. Is there an error message? (what is it?) or are results incorrect? how do you know that? please never use "it doesn't work" — Paul Maxwell, Nov 06 '17 at 22:29
You are right! my bad, I get an empty array, I don't know if that gives much more clarification .. :/ — John, Nov 06 '17 at 22:34
any chance you can provide sufficient sample data for us to run a query? similar to http://sqlfiddle.com/#!9/6f0766/1 but with the extra tables populated with some data. — Paul Maxwell, Nov 06 '17 at 22:38
So everything works until you added *budget*. And when you do, no error results, only an empty array. Likely then, there are no join matches to *budget*. Test it out in your MySQL console (workbench, phpmyadmin, etc.). — Parfait, Nov 06 '17 at 22:56
I have added a fiddle here: http://sqlfiddle.com/#!9/35a778/2 however i do not manage to get the sums in the 'budget' column to behave properly. I would like to see the sum for each subkategory, kategory, year etc. that are in the budget table. — John, Nov 07 '17 at 12:37

score 0 · Answer 1 · answered Nov 06 '17 at 22:43

This might help lead to an answer, here goes

Move the where clause

                       SELECT YEAR(transaktioner.datum) AS Year,
                       MONTH(transaktioner.datum) AS Month,
                       kategori.kat_namn as Kategori,
                       subkategori.subkat_namn as Subkategori,
                       SUM(transaktioner.belopp)  AS Belopp,
                       SUM(budget.budgetedAmmount) AS Budget

                FROM subkategori
                Left JOIN transaktioner 
                    ON subkategori.subkat_id=transaktioner.subkat_id
                Left  JOIN kategori
                    ON subkategori.kat_id = kategori.kat_id
                LEFT JOIN budget
                    ON subkategori.subkat_id = budget.subkat_id
    > here >>   WHERE ???
                GROUP BY Year, Month,Kategori,Subkategori
                WITH ROLLUP

Run that partial query in MySQL directly, if you get a result
run the complete query in MySQL, if you get a result
check that your PHP is injecting the year parameter correctly, just output the generated query code, and try to run that in MySQL

I'm out at this point, debugging PHP isn't "my thing"

Oh, just one small point the ANSI SQL equivalent function to IFNULL() is COALESCE(). While IFNULL() is fine in MySQL I would encourage you to use COALESCE() instead but you don't have to obviously.

Hmm didn't help.. thank you for the tip rgrd. Coalesce – John Nov 07 '17 at 16:17 — John, Nov 07 '17 at 16:17

score 0 · Answer 2 · answered Nov 15 '17 at 04:05

With some transactions monthly, and budget annually, there hs to be some method to spread he budget "per month". Also to ensure all sub categories and all months are reported create a "Cartesian product" of those, then left join the data to that. Finally in addition you need to include a way to sort the data sensibly. I hope that from this example you can adapt it to your specific needs.

SQL Fiddle

MySQL 5.6 Schema Setup:

-- Create transaktioner 
  CREATE TABLE transaktioner
      (`id` varchar(4),
      `datum` date,
      `beskrivning` varchar(8),
      `overforing` varchar(8),
      `belopp` varchar(8),
      `balans` varchar(8),
      `subkat_id` varchar(8),
      `konto_id` varchar(8)
       );

  INSERT INTO transaktioner
      ( `datum`,`beskrivning`,`overforing`,`belopp`,`balans`,`subkat_id`,`konto_id`)
  VALUES
      ("2017-01-01","Test1","Yes",'1000','1200','1','1111'),
      ("2016-01-01","Test1","Yes",'1000','1200','2','1111');

-- Create Kategori Table
  CREATE TABLE kategori
      (`kat_id` varchar(4),`kat_namn` varchar(10));

  INSERT INTO kategori
      ( `kat_id`,`kat_namn`)
  VALUES
      ('1',"Kat1"),
      ('2',"Kat2"),
      ('3',"Kat3"),
      ('4',"Kat4");


-- Create Subkategori Table   
  CREATE TABLE subkategori
      (`subkat_id` varchar(4),`kat_id` varchar(4),`subkat_namn` varchar(10));

  INSERT INTO subkategori
      ( `subkat_id`,`kat_id`,`subkat_namn`)
  VALUES
      ('1','1',"subKat1"),
      ('2','1',"subKat2"),
      ('3','1',"subKat3"),
      ('4','1',"subKat4");    

-- Create budget Table   
  CREATE TABLE budget
      (`budget_id` varchar(4),`subkat_id` varchar(4),`budgetedAmmount` varchar(10), `year` varchar(4));

  INSERT INTO budget
      ( `budget_id`,`subkat_id`,`budgetedAmmount`,`year`)
  VALUES
      ('1','1',"111",'2017'),
      ('2','2',"222",'2017'),
      ('3','3',"333",'2017'),
      ('4','4',"444",'2017'),
      ('1','1',"111",'2016'),
      ('2','2',"222",'2016'),
      ('3','3',"333",'2016'),
      ('4','4',"444",'2016');

Query 1:

SELECT
      IFNULL(Year, 'All')       AS Year
    , IFNULL(Month, 'All')      AS Month
    , IFNULL(Kategori,'All')    AS Kategori
    , IFNULL(Subkategori,'All') AS Subkategori
    , IFNULL(Belopp,0)          AS Belopp
    , IFNULL(Budget,0)          AS Budget
FROM (
      SELECT
            y.year                         AS year
          , m.n                            AS month
          , kategori.kat_namn              AS kategori
          , subkategori.subkat_namn                  AS subkategori
          , SUM(IFNULL(transaktioner.belopp,0))      AS belopp
          , SUM(IFNULL(b.budgetedAmmount,0))         AS budget
      FROM subkategori
      INNER JOIN kategori ON subkategori.kat_id = kategori.kat_id
      CROSS JOIN (
            SELECT 2017 year 
            ) y
      CROSS JOIN (
            SELECT 1 n union all SELECT 2 n union all SELECT 3 n union all SELECT 4 n union all 
            SELECT 5 n union all SELECT 6 n union all SELECT 7 n union all SELECT 8 n union all 
            SELECT 9 n union all SELECT 10  union all SELECT 11  union all SELECT 12
            ) m
      LEFT JOIN transaktioner ON subkategori.subkat_id = transaktioner.subkat_id
            AND y.year = YEAR(transaktioner.datum)
            AND m.n = MONTH(transaktioner.datum)
      LEFT JOIN (
                SELECT subkat_id, Year, budgetedAmmount/12 as budgetedAmmount
                FROM budget
                ) b ON subkategori.subkat_id = b.subkat_id
                    AND y.year = b.year
      GROUP BY
            y.year 
          , m.n
          , kategori.kat_namn 
          , subkategori.subkat_namn 
      WITH ROLLUP
      ) d
ORDER BY
      IFNULL(Year, 9999)
    , IFNULL(Month, 13)
    , IFNULL(Kategori,'zzzzzzzzz') 
    , IFNULL(Subkategori,'zzzzzzzzzzz')
;

Results:

| Year | Month | Kategori | Subkategori | Belopp | Budget |
|------|-------|----------|-------------|--------|--------|
| 2017 |     1 |     Kat1 |     subKat1 |   1000 |   9.25 |
| 2017 |     1 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     1 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     1 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     1 |     Kat1 |         All |   1000 |   92.5 |
| 2017 |     1 |      All |         All |   1000 |   92.5 |
| 2017 |     2 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     2 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     2 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     2 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     2 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     2 |      All |         All |      0 |   92.5 |
| 2017 |     3 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     3 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     3 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     3 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     3 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     3 |      All |         All |      0 |   92.5 |
| 2017 |     4 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     4 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     4 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     4 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     4 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     4 |      All |         All |      0 |   92.5 |
| 2017 |     5 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     5 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     5 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     5 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     5 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     5 |      All |         All |      0 |   92.5 |
| 2017 |     6 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     6 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     6 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     6 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     6 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     6 |      All |         All |      0 |   92.5 |
| 2017 |     7 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     7 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     7 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     7 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     7 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     7 |      All |         All |      0 |   92.5 |
| 2017 |     8 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     8 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     8 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     8 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     8 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     8 |      All |         All |      0 |   92.5 |
| 2017 |     9 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |     9 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |     9 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |     9 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |     9 |     Kat1 |         All |      0 |   92.5 |
| 2017 |     9 |      All |         All |      0 |   92.5 |
| 2017 |    10 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |    10 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |    10 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |    10 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |    10 |     Kat1 |         All |      0 |   92.5 |
| 2017 |    10 |      All |         All |      0 |   92.5 |
| 2017 |    11 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |    11 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |    11 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |    11 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |    11 |     Kat1 |         All |      0 |   92.5 |
| 2017 |    11 |      All |         All |      0 |   92.5 |
| 2017 |    12 |     Kat1 |     subKat1 |      0 |   9.25 |
| 2017 |    12 |     Kat1 |     subKat2 |      0 |   18.5 |
| 2017 |    12 |     Kat1 |     subKat3 |      0 |  27.75 |
| 2017 |    12 |     Kat1 |     subKat4 |      0 |     37 |
| 2017 |    12 |     Kat1 |         All |      0 |   92.5 |
| 2017 |    12 |      All |         All |      0 |   92.5 |
| 2017 |   All |      All |         All |   1000 |   1110 |
|  All |   All |      All |         All |   1000 |   1110 |

SQL group by and aggregation in two columns

2 Answers2