Good day list,
I've encountered a behaviour with 'WITH ROLLUP' that I don't understand.
SELECT a.report_date,
SUM(b.daytotal*last1) last1,
( 100*SUM(b.daytotal*last1) /
(select sum(daytotal) FROM aggregate_logs
WHERE aggr_source like 's%' and aggr_date=CURDATE()
)
) tper
FROM master.pivot_customer_overview a, aggregate_logs b
WHERE a.report_date=CURDATE()
AND a.actual_date=b.aggr_date
AND aggr_source LIKE 's%' GROUP BY aggr_source;
This produces
+-------------+-------+-------+
| report_date | last1 | tper |
+-------------+-------+-------+
| 2006-06-21 | 0 | 0.00 |
| 2006-06-21 | 14 | 14.29 |
| 2006-06-21 | 84 | 85.71 |
| 2006-06-21 | 0 | 0.00 |
| 2006-06-21 | 0 | 0.00 |
+-------------+-------+-------+
which is correct. 14 is 14.29% of 98.
When I apply a WITH ROLLUP after the GROUP BY clause, the output changes to
+-------------+-------+------+
| report_date | last1 | tper |
+-------------+-------+------+
| 2006-06-21 | 0 | 0 |
| 2006-06-21 | 14 | NULL |
| 2006-06-21 | 84 | NULL |
| 2006-06-21 | 0 | NULL |
| 2006-06-21 | 0 | NULL |
| 2006-06-21 | 98 | 100 |
+-------------+-------+------+
98 is indeed 100% of 98, but why have the the rest of the calculated columns
been determined as NULL? The docs on with rollup don't indicate (to me) why
this happens.
--
Scanned by iCritical.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
