CODEWITHSUNDEEP
sqlsqlite
user430574
user430574

Reputation: 315

SQLite - Getting SUM from a Identical ID

enter image description here

I am wanting to get the subtotal for each Unique Order ID. For example, 3 orders made by OrderID 10248 would total to

$168.0 + $98.0 + $174.0 = $440.0.

This is what I have so far in SQLite

SELECT OrderID, UnitPrice, Quantity, 
       Discount, '$' || ((UnitPrice * Quantity) - (Discount * (UnitPrice * Quantity))) 
    AS Subtotal
  FROM OrderDetail

I have tried GROUP BY OrderID, but that only gets the first Subtotal of each OrderID.

Any hints to getting the total would be appreciated.

Upvotes: 0

Views: 290

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Getting subtotals for each OrderID can be emulated by this query :

WITH OD AS
(
 SELECT OrderID, 
        UnitPrice, Quantity, Discount,
       (UnitPrice * Quantity) * ( 1 - Discount ) AS Subtotal
   FROM OrderDetail
)
SELECT * FROM OD 
UNION ALL
SELECT OrderID, Null, Null, Null, '$' || SUM( Subtotal ) 
  FROM OD
 GROUP BY OrderID  
 ORDER BY OrderID

Indeed, some other DB Management Systems use some functions and methods such as Cube(), Rollup() and GROUPING SETS().

Demo

Upvotes: 1

Related Questions