This is pretty much the same question as this one but I still can't make it work with my data.
I have two tables joined by fee_name. I'm selecting a fee, performing a group count, doing a calculation and getting a rollup total. My initial SQL works to get the numerical results I want.
However the rollup row shows the same fee (17.70) as the row above it, which is meaningless as the rollup total doesn't correspond to any fee. I am trying to get an empty string in the fee column for the roll up row. (similar to the other SO post)
Can anyone show me where I am going wrong and help me with the correct code for my tables?
I have shown three further attempts at getting the SQL right and the results, which still don't give me what I want, including one following advice from here (Note, those further attempts deliberately show the original fee column and add an extra column with my attempts to get an empty string in that column )
These are my two tables
bmc_fee - (only has 3 rows)
|fee_name | fee |
|Adult | 23.90 |
|Student | 19.75 |
|U18 | 17.70 |
bmctypes - (has about 600 rows)
|fee_name |
|Adult |
|Adult |
|Adult |
|Student |
|Adult |
|U18 |
|Student |
etc...
My initial SQL
SELECT
IF(GROUPING(bmctypes.fee_name), 'xTotal', bmctypes.fee_name) AS fee_name,
COUNT(*) AS num,
bmc_fee.fee,
bmc_fee.fee * COUNT(*) AS expense
FROM bmctypes
JOIN bmc_fee
ON bmc_fee.fee_name = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;
Expected Result (blank in the fee column for the xTotal row)
|fee_name | num | fee | expense |
|Adult | 560 | 23.90 | 13384.00 |
|Student | 3 | 19.75 | 59.25 |
|U18 | 10 | 17.70 | 177.00 |
|xTotal | 573 | | 10142.10 |
My initial results (17.70 in the fee column for the xTotal rollup row)
|fee_name | num | fee | expense |
|Adult | 560 | 23.90 | 13384.00 |
|Student | 3 | 19.75 | 59.25 |
|U18 | 10 | 17.70 | 177.00 |
|xTotal | 573 | 17.70 | 10142.10 |
Attempt 2, using IF() and adding an extra column to aid debugging
SELECT
IF(GROUPING(bmctypes.fee_name), 'xTotal', bmctypes.fee_name) AS fee_name,
COUNT(*) AS num,
bmc_fee.fee as fee_1,
IF(fee_name ='xTotal','',bmc_fee.fee) AS fee_2,
bmc_fee.fee * COUNT(*) AS expense
FROM bmctypes
JOIN bmc_fee
ON bmc_fee.fee_name = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;
Results of attempt 2 with IF()
|fee_name | num | fee_1 | fee_2 | expense |
|Adult | 560 | 23.90 | (NULL)| 13384.00 |
|Student | 3 | 19.75 | (NULL | 59.25 |
|U18 | 10 | 17.70 | (NULL | 177.00 |
|xTotal | 573 | 17.70 | (NULL)| 10142.10 |
Attempt 3 using a second GROUPING() and adding an extra column to aid debugging
SELECT
IF(GROUPING(bmctypes.fee_name), 'xTotal', bmctypes.fee_name) AS fee_name,
COUNT(*) AS num,
fee as fee_1,
IF(GROUPING(fee_name), '', bmc_fee.fee) AS fee_2,
bmc_fee.fee * COUNT(*) AS expense
FROM bmctypes
JOIN bmc_fee
ON bmc_fee.fee_name = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;
Results of attempt 3
|fee_name | num | fee_1 | fee_2 | expense |
|Adult | 560 | 23.90 | (NULL)| 13384.00 |
|Student | 3 | 19.75 | (NULL | 59.25 |
|U18 | 10 | 17.70 | (NULL | 177.00 |
|xTotal | 573 | 17.70 | | 10142.10 |
And attempt 4, following advice in the SO link
SELECT
COALESCE(bmctypes.fee_name,'xTotal') AS fee_name,
COUNT(*) AS num,
fee as fee_1,
(CASE WHEN bmctypes.fee_nameIS NULL THEN NULL ELSE fee END) AS fee_col_2,
bmc_fee.fee * COUNT(*) AS expense
FROM bmctypes
JOIN bmc_fee
ON bmc_fee.fee_name = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;
Results with COALESCE and CASE
|fee_name | num | fee_1 | fee_2 | expense |
|Adult | 560 | 23.90 | (NULL)| 13384.00 |
|Student | 3 | 19.75 | (NULL | 59.25 |
|U18 | 10 | 17.70 | (NULL | 177.00 |
|xTotal | 573 | 17.70 | (NULL)| 10142.10 |
Here's a solution:
SELECT
COALESCE(bmc_fee.fee_name, 'xTotal') AS fee_name,
COUNT(*) AS num,
IF(GROUPING(bmc_fee.fee_name), NULL, MAX(bmc_fee.fee)) as fee,
SUM(bmc_fee.fee) AS expense
FROM bmc_fee
JOIN bmctypes ON bmc_fee.fee_name = bmctypes.fee_name
GROUP BY bmc_fee.fee_name WITH ROLLUP;
Output given your sample data:
+----------+-----+-------+---------+
| fee_name | num | fee | expense |
+----------+-----+-------+---------+
| Adult | 4 | 23.90 | 95.60 |
| Student | 2 | 19.75 | 39.50 |
| U18 | 1 | 17.70 | 17.70 |
| xTotal | 7 | NULL | 152.80 |
+----------+-----+-------+---------+
Dbfiddle: https://www.db-fiddle.com/f/cu5YYscLmhUb6ZYnPUt2Zz/0
In your example desired output, the last line's expense is less than the sum of the expenses of each line. I guess it's because it's calculating the total count against a fee of 17.70, not the real sum. The query solution I show above sums the actual expenses.