SQL Server query to get the total for each row
Chathuranga Ranasinghe
I have two tables InvoiceLine and Discount:
I need to get a result set which includes invoice Line items with the total (SUM) of discounts in the discount table like below:
How can I achieve this using only one query?
Slawomir Cieslinski
DECLARE @InvoiceLine TABLE
(
InvoiceHeaderID INT ,
InvoiceLineNo INT ,
ProductCode VARCHAR(5) ,
Price MONEY
);
INSERT INTO @InvoiceLine
VALUES ( 1, 1, 'AB001', 1200 ),
( 2, 1, 'AC002', 1525 );
DECLARE @Discount TABLE
(
InvoiceHeaderID INT ,
InvoiceLineNo INT ,
DiscountCategory VARCHAR(10) ,
discountValue MONEY
);
INSERT INTO @Discount
VALUES ( 1, 1, 'SalesDisc', 120 ),
( 1, 1, 'FixedOffer', 100 ),
( 2, 1, 'SalesDisc', 152.50 );
SELECT l.InvoiceHeaderID ,
l.InvoiceLineNo ,
l.ProductCode ,
l.Price ,
ISNULL(SUM(d.discountValue),0) [TotalDiscount]
FROM @InvoiceLine l
LEFT JOIN @Discount d ON d.InvoiceHeaderID = l.InvoiceHeaderID
AND d.InvoiceLineNo = l.InvoiceLineNo
GROUP BY l.InvoiceHeaderID ,
l.InvoiceLineNo ,
l.ProductCode ,
l.Price;
Result
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: C++11 vector holding 2 different subclasses, but not at the same time
- Next: how to retreive from memory an object associated to a destroyed object
Related
Related Related
- 1
SQL Query: get adjusted value of each row
- 2
SQL Server Group By Query Select first row each group
- 3
Sql query to get 1 from each column row wise
- 4
sql query get total value
- 5
Trying to get total costs in each row jQuery
- 6
SQL Server query to get count value each day
- 7
How to get DISTINCT row from INNER JOIN Query in SQL Server
- 8
How to get DISTINCT row from INNER JOIN Query in SQL Server
- 9
How to get row with last day of month in Sql Server query
- 10
SQL Server create grand total row
- 11
SQL Group By Cube and get total of each combination
- 12
SQL COUNT - Advance, get the total of each record
- 13
sql get previous row value for a running total
- 14
SQL Server count query comparing results to total
- 15
T-SQL/XML Query to get for each row in the query a subfield as well as the whole lement
- 16
Adding values returned by SQL query to get the total?
- 17
Get each array into an SQL query
- 18
Get each array into an SQL query
- 19
GET total COUNT of all the count in SQL Server
- 20
How to get total time in SQL Server 2014
- 21
SQL Server : get the row Identity
- 22
SQL Server : get the row Identity
- 23
How to make a sql server query that converts one row with From Date To Date columns to multiple rows for each day or each month
- 24
SQL Server Add row number each group
- 25
sql server (insert row for each result nested)
- 26
Sql query to get total payments by customer type (1,2,3,4,5) in each month of given year
- 27
SQL Query To Show Total Each Time SalesMan Changes
- 28
SQL Query To Show Total Each Time SalesMan Changes
- 29
SQL sub query to retrieve total cost for each hotel
Comments