Page 1 of 1

SQL in Report doesn't work like in PhpMyAdmin

PostPosted: Thu May 21, 2015 7:31 am
by OLMGBG
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.

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

PostPosted: Tue May 26, 2015 3:10 pm
by admin
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 2062 times


Steven