sql query : sum of all rows and display in column

Question

Here is My query :

SELECT Name,
       Quantity,
       Price,
       (Quantity*Price)[Total Price] 
  FROM Menu,
       Items_per_Table,
       [735294] 
 WHERE Menu.Item_id = Items_per_Table.Item_id 
       AND [735294].OrderNo = Items_per_Table.OrderNo 
       AND Bill_Generated = 'True'

It returns :

Name                  Quantity   Price   Total Price
Aerated Drinks             1     30.00   30.00
Fresh Lime Soda/Water      3     60.00   180.00

Here I want the sum of total prices like this : 30+180 = 210 in another column. How can I perform that ?

It Should Return :

Name                  Quantity   Price   Total Price    All Over Price
Aerated Drinks             1     30.00   30.00          30
Fresh Lime Soda/Water      3     60.00   180.00         210

So _all_ rows should have the total price as an extra column? — Joachim Isaksson, Mar 08 '14 at 08:01
You're using `SQL Server` syntax but the query is tagged `MySQL`. Which is it? — Joachim Isaksson, Mar 08 '14 at 08:14
Person contacted with MySql may also be able to know this. Why don't i give a chance to answer them — Rocky Balboa, Mar 08 '14 at 08:15
A good answer is _very_ different for SQL Server and MySQL, so you should change the tags to clarify what you want. You want the answer for SQL Server then. Which version? 2012? — Joachim Isaksson, Mar 08 '14 at 08:16
A summing window over rows makes only sense, if the result set is sorted. For an answer to your question, see then http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql — Ulrich Thomas Gabor, Mar 08 '14 at 08:23
By the way, it's crucial which SQL software you are using, as there are notable differences between them. — Ulrich Thomas Gabor, Mar 08 '14 at 08:23

Joachim Isaksson · Answer 1 · 2014-03-08T08:38:41.250

For SQL Server 2012, this should do what you need;

SELECT Name,
       Quantity,
       Price,
       (Quantity*Price) [Total Price],
       SUM(Quantity*Price) OVER (ORDER BY menu.item_id) [All Over Price] 
  FROM Menu,
       Items_per_Table,
       [735294] 
 WHERE Menu.Item_id = Items_per_Table.Item_id 
       AND [735294].OrderNo = Items_per_Table.OrderNo 
       AND Bill_Generated = 'True'
ORDER BY menu.item_id

SUM(...) OVER (ORDER BY...) does the running sum in the order you give it. Since you have no order in your question, I arbitrarily chose menu.item_id to order by.

EDIT: Forgot to add the SQLfiddle to test with.

score 0 · Answer 2 · answered Mar 08 '14 at 10:55

Try using Common Table Expressions,(If you are using SQL < SQl 2012)

;with cteabc
as (
SELECT Name,
       Quantity,
       Price,
       (Quantity*Price)[Total Price] 
  FROM Menu,
       Items_per_Table,
       [735294] 
 WHERE Menu.Item_id = Items_per_Table.Item_id 
       AND [735294].OrderNo = Items_per_Table.OrderNo 
       AND Bill_Generated = 'True'
    )
select
    t1.Name,
    t1.Quantity,
    t1.Price,
    rt.runningTotal
from cteabc t1
 cross apply (select sum([Total Price]) as runningTotal
                from cteabc t2
                where  t2.rn<= t1.rn
            ) as rt
order by t1.Name

SQL Fiddle

sql query : sum of all rows and display in column

2 Answers2