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]