Gary Brett asked 9 years ago
Hi Abu, I have a query which runs fine on other projects and indeed inside phpmyadmin but not inside the grid. The grid doesn't like with rollup, any way around that?
In phpmyadmin it sums up the columns at bottom
From
tbl_invoice_data
Group By
adviser with rollup
Error it throws is 'Couldn't execute query. Incorrect usage of CUBE/ROLLUP and ORDER BY' even though I am not using an order by..
Cheers
Gary Brett answered 9 years ago
HI Abu, to follow this up I also note that using the grid it adds an extra column called 'Null', I cant even remove that using show hide. This query works fine in phpmyadmin and on other php projects I used so a bit confused why these 2 issues appear in the grid?
Code is here – http://pastebin.com/383gz4iD
Thanks
Abu Ghufran answered 9 years ago
You can try using main query inside subquery.
$g->select_command = "SELECT * FROM (SELECT client_id, SUM(total) FROM `invheader` GROUP BY client_id WITH ROLLUP) as t";
This way, order by will be applied to external and not conflict the rollup.
Gary Brett answered 9 years ago
Thanks Abu, that sorted it after I set the sort in grid to't.Total ASC'.
Any ideas why the gris also places a NULL column on the end, this doesnt occur in plan php or phpmyadmin?
Thanks
Abu Ghufran answered 9 years ago
The following query also shows a null with sum row in phpmyadmin.
SELECT client_id, SUM(total) FROM `invheader` GROUP BY client_id WITH ROLLUP
May be I don't understand, Share screenshot.
