MySQL with rollup

QuestionsMySQL with rollup
Gary Brett asked 4 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

4 Answers
Gary Brett answered 4 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 4 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 4 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 4 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.

Your Answer

2 + 17 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!