sorting pages from sql statements with SUM() and/or COUNT()

Talk about Sorting & Paging problems, issues etc.

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

malaikamkuu
Newbie
Newbie
Posts: 2
Joined: Jul 23rd, '09, 09:11

sorting pages from sql statements with SUM() and/or COUNT()

Postby malaikamkuu » Jul 23rd, '09, 09:20

Hello,
I have an sql querry like this "SELECT id, date, COUNT(id) AS transactions, SUM(amount) AS cash FROM transaction WHERE user_type='CUSTOMER' GROUP BY username"

the querry is to get unique users that transacted. and how many times they transacted and how much money they spent. The results get displayed just fine, and when I use the $default_order_field to transactions, they get ordered properly, same when i use cash.

But when I click the sorting tab in the datagrid, for the SUM(cash) it does not sort properly, and for the COUNT(id), it doesn't sort at all, I get an error. After setting the debug mode to true, I realised that the order by querry is not added correctly. For SUM function the order by is added as order by ABS(amount), and for COUNT its added as order by ABS(9). Both of these are wrong. Please help!!!!

the correct query should say order by transactions, (when i sort by transactions) and should say order by cash (when i sort by cash). Help

administrator
Site Admin
Site Admin
Posts: 5957
Joined: Jan 7th, '09, 23:18
Contact:

Re: sorting pages from sql statements with SUM() and/or COUNT()

Postby administrator » Jul 23rd, '09, 16:41

Eplain please what error you get and did you set "sort_type"=>"numeric" for appropriate fields?

malaikamkuu
Newbie
Newbie
Posts: 2
Joined: Jul 23rd, '09, 09:11

Re: sorting pages from sql statements with SUM() and/or COUNT()

Postby malaikamkuu » Jul 24th, '09, 05:33

Yes, I set the sort_type to numeric in both fields.

The error I get when I click the tab for transactions (which uses the COUNT(id) in sql) I get this error:

-----------------------debug mode------------------------------------
search sql (total: 20) SELECT id, date, service, SUM(amount) AS amount, SUM(commission) AS commission, medium, staff AS username, to_account, COUNT(id) AS transactions FROM transaction WHERE staff LIKE '012%' GROUP BY staff ORDER BY ABS(9) asc LIMIT 0, 20


Fatal error: Call to undefined method DB_Error::tableInfo() in /opt/lampp/htdocs/myapp/modules/datagrid/datagrid.class.php on line 5065

----------------------------------end-----------------------


And when I click the tab for amount (which uses the SUM(amount) in sql), I don't get an error, but the values are not sorted properly, and the queries generated when i enable debug mode is this:

-----------------------debug mode-------------------------
search sql (total: 20) SELECT id, date, service, SUM(amount) AS amount, SUM(commission) AS commission, medium, staff AS username, to_account, COUNT(id) AS transactions FROM transaction WHERE staff LIKE '012%' GROUP BY staff ORDER BY ABS(4) asc LIMIT 0, 20

search sql (total: 20) SELECT id, date, service, SUM(amount) AS amount, SUM(commission) AS commission, medium, staff AS username, to_account, COUNT(id) AS transactions FROM transaction WHERE staff LIKE '012%' GROUP BY staff ORDER BY ABS(amount) asc LIMIT 0, 20

----------------------------------------------end----------------------------

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Re: sorting pages from sql statements with SUM() and/or COUNT()

Postby BlackLocust » Oct 3rd, '09, 01:13

I added a tweak in my version of the code to have a "custom" sort. This causes the grid to act like it is sorting, but let the SQL that's passed in do the ORDER BY statement.

For me, this has been a good compromise ... and has let me sort past all kinds of JOIN's and summary operations. Also lets the DB do the sorting of the date fields.


Return to “Settings: Sorting & Paging”