CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Annoscia
Annoscia

Reputation: 117

Finding the total amount for my query

I have a query that allows me to retrieve customer orders.

Select ID, OrderID, Item, Price from customerOrders
Where Order = 1

is there a way of totalling up the final amount to pay and have it visible below, but still be able to view all details needed in the sql query, for example

ID    |  OrderID |  Item  | Price
1     |   1      |  Book  | 9.99
2     |   1      |  DVD   | 12.99

                    total = 22.98

Many thanks in advance

Upvotes: 1

Views: 68

Answers (4)

Andriy M
Andriy M

Reputation: 77707

Technically, the thing you want is called a roll-up row. SQL Server has a special function for such cases, it's called ROLLUP() and used inside a GROUP BY clause. In your particular situation, the query would basically look like this:

SELECT ID, OrderID, Item, SUM(Price) AS Price
FROM customerOrders
WHERE OrderID = 1
GROUP BY ROLLUP((ID, OrderID, Item))
;

You can see a live demonstration of this query at SQL Fiddle. More information about ROLLUP(), as well as other GROUP BY functions, can be found at MSDN.

Upvotes: 1

Rikki
Rikki

Reputation: 3528

This is what you need:

   Select ID, OrderID, Item, Price from customerOrders
   Where Order = 1
Union
   Select NULL, NULL, NULL, (
      Select Sum(Price) from customerOrders
      Where Order = 1
      Group By  Order
   )

UPDATE

Incase the result was empty, the total should be zero, not empty. Use the updated code.

Cheers

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

Two steps:

1, To generate the SUM, you need to add it to the result set using UNION ALL. UNION adds a DISTINCT operation which isn't required, even if in your case it won't change the result.

Select ID, OrderID, Item, Price from customerOrders
Where [Order] = 1
UNION ALL
Select NULL, NULL, NULL, SUM(Price) from customerOrders
Where [Order] = 1
ORDER BY CASE WHEN ID IS NULL THEN 2 ELSE 1 END, ID;

2, To sort it in the order you want (sum at the bottom), you can use the NULL ID as an identifier.

Upvotes: 1

EricZ
EricZ

Reputation: 6205

You can try COMPUTE

Select ID, OrderID, Item, Price from customerOrders
Where Order = 1
COMPUTE SUM(Price)

Upvotes: 1

Related Questions