SQL in Report doesn't work like in PhpMyAdmin

[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.

[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 (25.76 KiB) Viewed 2968 times

Steven