Referring to a column in the first query from the second query in T-SQL UNION
I have two SELECT statements that are UNIONed, as in the pseudo code below
SELECT A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost
FROM someTable A
UNION
SELECT InvoiceNumberFromSomeTable, B.itemCode, B.itemDescription, ***IwantTheSumOfAllCostsInTheFirstQueryForThisInvoiceNumber*** as Cost
FROM yetAnotherTable B
The expected result is
InvoiceNumber itemCode itemDescription Cost
I001 A000001 This is Item01 From SomeTable 15
I001 A000002 This is Item02 From SomeTable 16
I001 Total This is Total From YetAnotherTable 31
I002 A000001 This is Item01 From SomeTable 25
I002 B000002 开发者_开发知识库 This is Item99 From SomeTable 26
I002 Total This is Total From YetAnotherTable 51
In my case, the second query will always return one row and the Cost value should be the sum of all the Cost Values in first query.
How can one accomplish this? I've tried every approach I'm familiar with, but couldn't make it work. Please help and Thank you SOF community.
EDIT: I forgot to add the invoice number column. The totalling should happen for each invoice. Apologies for not adding this earlier.
What you can do is to SUM as an extra column
SELECT
A.itemCode, A.itemDescription, A.Cost,
SUM(A.Cost) OVER () AS SumCostA
FROM someTable A
UNION
SELECT B.itemCode, B.itemDescription,
Whatever1, Whatever2 --NULLs?
FROM yetAnotherTable B
It doesn't that much sense to have yetAnotherTable itemCode and itemDescription related to data from another table. What about Cost and Sum(Cost) from yetAnotherTable?
Is yetAnotherTable parent or category table? If so, a JOIN (no UNION) would make more sense
Edit, after update
SELECT
A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost,
SUM(A.Cost) OVER (PARTITION BY A.InvoiceNumber) AS SumCostAPerInvoice
FROM someTable A
UNION
SELECT InvoiceNumberFromSomeTable, B.itemCode, B.itemDescription,
Whatever1, Whatever2 --NULLs?
FROM yetAnotherTable B
SELECT InvoiceNumber, itemCode, itemDescription, Cost
FROM
(
SELECT A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost, 1 dummy
FROM someTable A
UNION
SELECT A.InvoiceNumber, 'Sub total', B.itemDescription, SUM(A.Cost), 2
FROM yetAnotherTable B
JOIN someTabel A ON A.InvoiceNumber = B.InvoiceNumberFromSomeTable
GROUP BY A.InvoiceNumber, B.itemDescription
) a
ORDER BY InvoiceNumber,dummy
You should also try this. Although it will not give you the itemdescription from your second table
http://msdn.microsoft.com/en-us/library/ms177673.aspx
精彩评论