Hello Harrison.

This is SQL, not OutSystems.

1. You make a query for sales.
2. You make another query for Subtotals.
3. You union them.
4. You place a strategic field to help you order subtotals at the bottom of the date.

It will look something like this:

select Date, Details, Amount
from (
select Date, Details, Amount, 0 as orderby
from Sales
union all
select Date, '' as Details, sum(Amount) as Amount, 1 as orderby
from Sales
group by Date
) as a
order by Date, orderby, Details

Hello Harrison,

As mentioned by @Nuno Reis, you need to use the query given by him into the Advance SQL in OutSystems and then display the result on the screen.

If you need any help, please let us know.

Thanks & Kind Regards,

Sachin

Thanks for your responses. I have followed the guide above but unable to get the desired result. See the error message after implementing the SQL recommended below: 

Please kindly help look the SQL above and help resolve the problem for me.

The Aggregate table is  below:

Kindly help do the required SQL with the aggregate presented above. Thanks

On the first and last lines of code, it is not {Sales}. The table name is a.

Replace it with 

Select a.Data, a.Details, a.Amount

...

Order by a.Date, a.orderby, a.Details

Thanks for your response. Table name is Sales  (see the aggregate above).

Hi 

I don't think you need subquery for this.

select {Sales}.[Date], {Sales}.[Details], {Sales}.[Amount], 0 as orderby
from {Sales}
union all
select {Sales}.[Date], '' as Details, sum({Sales}.[Amount]) as Amount, 1 as orderby
from {Sales}
group by {Sales}.[Date]
order by [Date], orderby, [Details]

Kind regards,

Thanks to everyone that took out time to respond to my question. Nuno and Sachin, your  inputs are appreciated. Special thanks to Tom, the SQL statement you recommended is working as desired. Thanks again.