Re: Select with If and With Rollup

[Peter Brawley]

> I'm not able to add files or pics

SQL is text, files & pics don't help.

The complete pivot table for your example is ...

SELECT 
  type, id , 
  If(type = 1, SUM(value),0) AS type1sum,
  If(type = 2, SUM(value),0) AS type2sum,
  sum(value)
FROM `test`   
GROUP BY type, id WITH ROLLUP;
+------+------+----------+----------+------------+
| type | id   | type1sum | type2sum | sum(value) |
+------+------+----------+----------+------------+
|    1 |    1 |      100 |        0 |        100 |
|    1 |    2 |        2 |        0 |          2 |
|    1 | NULL |      102 |        0 |        102 |
|    2 |    3 |        0 |       10 |         10 |
|    2 |    4 |        0 |       20 |         20 |
|    2 | NULL |        0 |       30 |         30 |
| NULL | NULL |        0 |        0 |        132 |
+------+------+----------+----------+------------+

As you can see, rowsums---here, sum(value)---are needed for the bottom right grand total.

For an intro to pivot tables see "Pivot table basics: rows to columns" at https://www.artfulsoftware.com/infotree/qrytip.php.