SQL in Report doesn't work like in PhpMyAdmin

SQL in Report doesn't work like in PhpMyAdmin

Postby OLMGBG » Thu May 21, 2015 7:31 am

Hi All,

I've spend a lot of time testing why my report doesn't work as expected.
I'm trying to get subtotals in my report.
Expected result should be like this :
----------------------------------------------------
numRec text Value type amount
----------------------------------------------------
Type A
1 aaa A 10
5 bbb A 20
3 xxx A 10
2 ccc A 10
Sub Total A 50
Type B
4 hhh B 30
6 ddd B 20
Sub Total B 50
Total 100

the table is something like this :
numRec INT
textValue VARCHAR 25
type VARCHAR 10
amount FLOAT


my SQL command is in the SQL zone of the report (tried to put it in PhP but doesn't work better)

SELECT * from myTable
GROUP BY numRec, type
Order By type


in my report I have put 2 groups because I want both total and sub total and I want to get detail of each line.

The issue is that I can't have data sorted by type, just as if Group By and Order By couldn't be used in the same SQL command.

Thanks for your help.

Kind regards,
OLMGBG.
OLMGBG
 
Posts: 7
Joined: Sun Feb 15, 2015 12:11 am

Re: SQL in Report doesn't work like in PhpMyAdmin

Postby admin » Tue May 26, 2015 3:10 pm

OLMGBG,

When you create a #TABLE_ID# table for report, there is no need to sirt or group by.

The Report Writer will do this automatically when you create a Report Group.

c1.PNG
c1.PNG (25.76 KiB) Viewed 1805 times


Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am


Return to Report Writer



cron