CODEWITHSUNDEEP
sqloracle-database
Lawrencxe
Lawrencxe

Reputation: 81

Get the total discount using a sql query

I have a query that computes the discount of each item. Now I want is get the total of the discount. How can I do that?

My query to compute the discount is:

select (dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty as discount
       , dla.itemname as item_name 
 from doo_lines_all dla

For example, below is the returned data of the query I did.

Item_Name        Discount
Item1            50
Item2            25
Item3            30

Now I want is to get the total discount which I expected result is 105

Upvotes: 0

Views: 1353

Answers (5)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

You can use grouping sets

with doo_lines_all( discount, item_name ) as
(
 select 50, 'Item1' from dual union all
 select 25, 'Item2' from dual union all
 select 30, 'Item3' from dual
)
select item_name, 
       sum( discount ) as discount 
  from doo_lines_all dla
 group by grouping sets(item_name,())
 order by item_name;

ITEM_NAME   DISCOUNT
---------   --------
Item1       50
Item2       25
Item3       30
            105

to return the sum of discounts as a seperate row.

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74740

You'll have to forego the item name:

select sum((dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty) as totaldiscount
from doo_lines_all dla

This will produce just a single output (one row, one column) of "105"

If you want to keep the item name in there and have the lines show the individual discount and a repeating total we can use an analytic:

select 
  dla.itemname,
  (dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty as discount,
  sum((dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty) over() as totaldiscount

from doo_lines_all dla

This will produce an output like:

Item_Name        Discount  TotalDiscount
Item1            50        105
Item2            25        105
Item3            30        105

There are other ways of achieving the same thing but an analytic function is perhaps the simplest to write, though perhaps harder to understand than doing this:

select 
  dla.itemname,
  (dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty as discount,
  t.totaldiscount

from doo_lines_all dla
cross join
(
  select sum((unit_list_price + unit_selling_price) * ordered_qty) as totaldiscount
  from doo_lines_all
) t

This works out the total in a subquery and then joins it to every row from dla via a cross join. You could conceive that it works the same as the analytic/it's one way to think of how the analytic works

Upvotes: 1

Weeway
Weeway

Reputation: 11

try to use the "SUM" function like this

SUM((dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty) as discount

Upvotes: 1

hotfix
hotfix

Reputation: 3396

you can use SUM analytical function

with tab as(
  select 'item1' as item , 50 as discount from dual union all
  select 'item2' as item , 25 as discount from dual union all
  select 'item3' as item , 30 as discount from dual 
)
select t.*
     , sum(t.discount) OVER () AS total_discount
from tab t;

ITEM  | DISCOUNT | TOTAL_DISCOUNT
:---- | -------: | -------------:
item1 |       50 |            105
item2 |       25 |            105
item3 |       30 |            105

db<>fiddle here

Upvotes: 0

Ori Marko
Ori Marko

Reputation: 58902

Sum the query:

select sum((dla.unit_list_price + dla.unit_selling_price) * dla.ordered_qty) as discount
       , dla.itemname as item_name 
 from doo_lines_all dla

SUM returns the sum of values of expr.

Upvotes: 1

Related Questions