Give me a guide on how to filter sales data by date and get total by date
Application Type
Traditional Web

Help guide me on how to filter sales data by date and get total by date

The sales data is presently like the one below:

Date           Details       Amount

2/01/21      Tv set             500

2/01/21      Radio set       200

3/02/21      Books             50

2/02/21     Glass               100

7/02/21      HP Printer      40

4/02/21     Bag                   30

                                           920


Is it possible to filter the sales table above to look like the one below ?:

Date           Details       Amount

2/01/21      Tv set             500

2/01/21      Radio set       200

                                           700

3/02/21      Books             50

2/02/21     Glass               100

7/02/21      HP Printer      40

4/02/21     Bag                   30

                                            220

Please, help give me a guide on how to filter sales data by dates with sub totals.

Thanks alot

Champion
Solution

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,

mvp_badge
MVP

That's right. Thanks for the improvement Tom!

mvp_badge
MVP

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

Could you please try this?

select a.[Date], a.[Details], a.[Amount]
from (
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]
) as a
order by a.[Date], a.[Details]

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

mvp_badge
MVP

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

mvp_badge
MVP

Notice this piece of code. You said "as a" so the entire From is made to a table called 'a'.

Both Select and Order By must use that designation.

Champion
Solution

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,

mvp_badge
MVP

That's right. Thanks for the improvement Tom!

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. 

Champion

Glad I could help. You are very welcome.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.