Sum of rows in SQL with GROUP BY

Question

I am having a table with data like:

ID  OrderId TAX%    Quantity UnitCost
1     5      28    2       1280
2     5      18    1       1180

I want the output like:

ORDERID    TaxableValue       TaxValue
   5           3000               740

How can I achieve this? I tried with:

SELECT orderid,
       ROUND((SUM(UnitCost*Quantity)*TAX)/(100+TAX),2) AS taxValue,
       ROUND(SUM(unitCost*quantity)-ROUND((SUM(UnitCost*Quantity)*TAX)/(100+TAX),2),2) AS taxableValue 
FROM table1 
GROUP BY OrderId;

But the query above is not working.

Sorry all, That is the Tax percentage. This is the new Updated Query. Please consider this.

i am getting data like Order Id -> 5, tax Value->81.81, taxable value->292.19 — SaiRam
– SaiRam, Commented Nov 14, 2017 at 15:32

Sean Lange · Accepted Answer · 2017-11-14 15:23:05Z

2

Pretty sure it is as simple as doing a little math.

select OrderID
    , TaxableValue = SUM(UnitCost - TAX)
    , TaxValue = SUM(TAX * Quantity)
from YourTable
group by OrderID

EDIT: The math was slightly off on the TaxableValue

For those who want proof that this works.

declare @Something table
(
    ID int
    , OrderId int
    , TAX int
    , Quantity int
    , UnitCost int
)

insert @Something values
(1, 5, 28, 2, 228)
, (2, 5, 18, 1, 118)

select OrderID
    , TaxableValue = SUM(UnitCost - TAX)
    , TaxValue = SUM(TAX * Quantity)
from @Something
group by OrderID

edited Nov 14, 2017 at 15:23

answered Nov 14, 2017 at 15:11

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

S3S Over a year ago

You bet brother.

Edward N Over a year ago

I'm doubt about your output with OP's expected result

Edward N Over a year ago

@SeanLange It's nice, you got it

Sean Lange Over a year ago

@SaiRam again....the query posted in this answer is EXACTLY what you stated you want as output. If it is not correct then you need to update your question and explain what you want.

Sean Lange Over a year ago

Then can you update your desired output based on this?

|

Ajith · Accepted Answer · 2017-11-14 15:51:56Z

0

This is how it can be done.

SELECT 
orderid, 
sum(unitcost*quantity) - sum(tax*quantity) as taxablevalue,
sum(tax*quantity) as taxvalue
FROM table1  
GROUP BY OrderId;

answered Nov 14, 2017 at 15:51

Ajith

2,5192 gold badges16 silver badges6 bronze badges

Comments

SaiRam · Accepted Answer · 2017-11-14 18:13:33Z

0

select OrderId
    , SUM((unitcost * quantity * tax)/(100+tax)) as TaxValue
    , SUM(unitcost * quantity)-SUM((unitcost * quantity * tax)/(100+tax)) as taxableValue
from table1
group by OrderId

edited Nov 14, 2017 at 18:13

user6655984

answered Nov 14, 2017 at 17:33

SaiRam

12 bronze badges

Collectives™ on Stack Overflow

Sum of rows in SQL with GROUP BY

3 Answers 3

19 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

19 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related