CODEWITHSUNDEEP
sqlsql-server
James123
James123

Reputation: 11662

Calculate column amount and get total with order number?

How to calculate each order number amount and total amount with SQL status

Order_no    Status Amount
9008258656  P      50.00
9008258656  P      0.00
9008510713  P      50.00
9008510713  P      0.00

Upvotes: 0

Views: 285

Answers (2)

Brooke Goldstein
Brooke Goldstein

Reputation: 86

If you are looking to keep your individual line numbers (i.e. 4 total records) and not have aggregates (i.e. 2 total records), you can use the sum window function. 

SELECT ord.Order_no
     , ord.Status
     , ord.Amount 
     , TotalSum = SUM(ord.Amount)OVER(PARTITION BY ord.Order_no, ord.Status)
FROM Orders ord

This would produce the following result:

Order_no    Status  Amount  TotalAmount
9008258656  P       50.00   50.00
9008258656  P       0.00    50.00
9008510713  P       50.00   50.00
9008510713  P       0.00    50.00

Based off the example you provided, there probably is not much value in doing the sum over in this scenario. @GMB's response should suffice. However, there are a lot of cool things you can do with the sum window function such as running totals. For example, if you had an order date column, you can include after the PARTITION BY ord.Order_no, ord.Status ORDER BY ord.Order_date and this would give you a running sum of the amount that increments by each order. I.E: 

Order_no    Order_date  Status  Amount  RunningTotal
9008258656  1/2/2019    P       50.00   50.00
9008258656  1/3/2019    P       0.00    50.00
9008258656  1/4/2019    P       50.00   100.00
9008258656  1/5/2019    P       0.00    100.00

Upvotes: 1

GMB
GMB

Reputation: 222682

Well, it looks like you want a simple aggregated query :

SELECT order_no, count(*) number_of_orders, sum(amount) total_amount
FROM orders
GROUP BY order_no

If you need to filter on a specific status :

SELECT order_no, count(*) number_of_orders, sum(amount) total_amount
FROM orders
WHERE status = 'P'
GROUP BY order_no

Upvotes: 1

Related Questions