April 12, 2006 at 4:47 pm
Hello,
I am really stuck and maybe I am just looking at this incorrectly...
I want to return a record set using the following 3 queries:
SELECT SUM(exten_price) AS SALESY, SUM(profit) AS PROFITY
FROM dbo.invoice
WHERE (contractyn = 'y')
SELECT SUM(exten_price) AS SALESN, SUM(profit) AS PROFITN
FROM dbo.invoice
WHERE (contractyn = 'n')
SELECT SUM(exten_price) AS SALES, SUM(profit) AS PROFIT
FROM dbo.invoice
The record set needs to be displayed something like:
SALES PROFIT SALESY PROFITY SALESN PROFITN
10 20 5 10 5 10
20 40 null null 20 40
18 20 18 20 null null
50 50 25 25 25 25
Here is the kicker.. I will be dynamically passing in the WHERE Clause. So I don't think I can use JOINS. But I am up for suggestions. I have tried: JOINS, UNION, "creative" SP, but still stuck.
Any help is greatly appreciated.
T
April 12, 2006 at 5:09 pm
SELECT
Sales, Profit,
SUM(CASE WHEN <condition1> THEN Sales END) AS SalesY,
SUM(CASE WHEN <condition1> THEN Profit END) AS ProfitY,
SUM(CASE WHEN <condition2> THEN Sales END) AS SalesN,
SUM(CASE WHEN <condition2> THEN Profit END) AS ProfitN
FROM dbo.invoice
GROUP BY Sales, Profit
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply