开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜