question

Doudy_2013 avatar image
Doudy_2013 asked

Sql query to get sum of Amounts based on the Bill number

I have my table datas as follows: ProductID SupplierID BillNo ProductName Price Quantity 1 1 Cs34 Black T-shirt 250 20 2 34 CD01 Jeans 450 40 3 1 Cs34 Green T-shirt 250 20 4 1 Cs34 Yellow T-shirt 250 20 I want to get sum of Amounts based on the BillNo, My output will be like that: BillNo TotalAmount Cs34 (250*20)+(250*20)+(250*20) = 15000 CD01 (450*40) = 18000 So can any one tell how can I get the total sum of those in sql server 2008 please.
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
So you want to do your calculations and GROUP the result BY BillNo: DECLARE @TableData TABLE ( ProductID INT , SupplierID INT , BillNo VARCHAR(10) , ProductName VARCHAR(50) , Price INT , Quantity INT ) INSERT INTO @TableData VALUES ( 1, 1, 'Cs34', 'Black T-shirt', 250, 20 ), ( 2, 34, 'CD01', 'Jeans', 450, 40 ), ( 3, 1, 'Cs34', 'Green T-shirt', 250, 20 ), ( 4, 1, 'Cs34', 'Yellow T-shirt', 250, 20 ) SELECT BillNo , SUM(Quantity * Price) AS TotalAmount FROM @TableData GROUP BY BillNo
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
SQL is a great calculator. You can use something such as SELECT column1, column2, sum(column3) WHERE BillNo = 'value'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.