asked on

Merge results from a query

Hi,

I really feel I am missing something obvious here but this is a summary example of our problem.

We have a query that returns two columns - Account and Value. We run this query 6 times passing different values into it which changes the return of "Value" and is grouped by Account.

When we run this as 6 independant queries in SQL Analyser (all at once) the values are returned within 3 seconds. When we start trying to JOIN the tables it takes 14 seconds so I am trying to discover a way of merging the results from all 6 queries on one line grouped, again, by Account.

Hope that makes some sense.

Thanks.

James.

Hilaire

Could you post your code ?

muzzy2003

Almost certainly possible as a single SELECT if you post your code, possibly involving cases inside the aggregate functions.

JAMES

ASKER

OK - here are the individual select statements (5 in total for this example):-

SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',0, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange

SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) FROM UpDownProfitOrLossByAccount('JAMES','1','20041123',10, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange

SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) FROM UpDownProfitOrLossByAccount('JAMES','1','20041123',20, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange

SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) FROM UpDownProfitOrLossByAccount('JAMES','1','20041123',-10, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange

SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) FROM UpDownProfitOrLossByAccount('JAMES','1','20041123',-20, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange

Thanks.

James.

JAMES

ASKER

Sorry they should all read "FROM TicketsWithPricesAndProfitOrLoss" not "UpDownProfitOrLossByAccount".

I copied the wrong bit from SQL Analyzer.

Hilaire

Can you give it a try ?
Maybe you'll need to change the select and the group by clause if you need a different group level

Select Account , Contract, Currency, Exchange, Sum(ProfitOrLoss)
FROM (
SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) as ProfitOrLoss FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',0, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange
UNION ALL
SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) as ProfitOrLoss FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',10, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange
UNION ALL
SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) as ProfitOrLoss FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',20, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange
UNION ALL
SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) as ProfitOrLoss FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',-10, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange
UNION ALL
SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) as ProfitOrLoss FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',-20, 0, 0, 'LME', '20040218', '20000101')GROUP BY Account, Contract, Currency, Exchange
) derived
GROUP BY Account , Contract, Currency, Exchange

JAMES

ASKER

It runs but I may need to explain a little more about the results required.

We need the ProfitOrLoss figures from each "select" to be shown as individual columns not added together.

Thanks.

James.

Hilaire

Could you give it a try ?

Select Account , Contract, Currency, Exchange,
Sum(case when pivot = 0 then ProfitOrLoss else 0 end) as ProfitOrLoss_0,
Sum(case when pivot = 10 then ProfitOrLoss else 0 end) as ProfitOrLoss_10,
Sum(case when pivot = 20 then ProfitOrLoss else 0 end) as ProfitOrLoss_20,
Sum(case when pivot = -10 then ProfitOrLoss else 0 end) as ProfitOrLoss_M10,
Sum(case when pivot = -20 then ProfitOrLoss else 0 end) as ProfitOrLoss_M20
FROM (
      SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss) as ProfitOrLoss, 0 as pivot
      FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',0, 0, 0, 'LME', '20040218', '20000101')
      GROUP BY Account, Contract, Currency, Exchange
      UNION ALL
      SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss), 10
      FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',10, 0, 0, 'LME', '20040218', '20000101')
      GROUP BY Account, Contract, Currency, Exchange
      UNION ALL
      SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss), 20
      FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',20, 0, 0, 'LME', '20040218', '20000101')
      GROUP BY Account, Contract, Currency, Exchange
      UNION ALL
      SELECT Account, Contract, Currency, Exchange, Sum(ProfitOrLoss), -10
      FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',-10, 0, 0, 'LME', '20040218', '20000101')
      GROUP BY Account, Contract, Currency, Exchange
      UNION ALL
      SELECT Account, Contract, Currency, Exchange, (ProfitOrLoss), -20
      FROM TicketsWithPricesAndProfitOrLoss('JAMES','1','20041123',-20, 0, 0, 'LME', '20040218', '20000101')
      GROUP BY Account, Contract, Currency, Exchange
) derived
GROUP BY Account , Contract, Currency, Exchange

JAMES

ASKER

Doesnt run - error is :-

Server: Msg 8120, Level 16, State 1, Line 1
Column 'TicketsWithPricesAndProfitOrLoss.ProfitOrLoss' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JAMES

ASKER

I think you missed the Sum off the last Select - I put it in and here are the results :-

4DH      ALHG      USD      LME      0.0      0.0      920067.43000000063      0.0      0.0
4DH      ALHG      USD      LME      0.0      942567.43000000052      0.0      0.0      0.0
4DH      ALHG      USD      LME      965067.43000000063      0.0      0.0      0.0      0.0
4DH      ALHG      USD      LME      0.0      0.0      0.0      987567.43000000063      0.0
4DH      ALHG      USD      LME      0.0      0.0      0.0      0.0      1010067.4300000006
4MM      ALHG      USD      LME      0.0      0.0      0.0      0.0      1286296.5
4MM      ALHG      USD      LME      0.0      0.0      0.0      1511296.5      0.0
4MM      ALHG      USD      LME      1736296.5      0.0      0.0      0.0      0.0
4MM      ALHG      USD      LME      0.0      1961296.5      0.0      0.0      0.0
4MM      ALHG      USD      LME      0.0      0.0      2186296.5      0.0      0.0

We need them grouped by Account.

Sorry to be a pain but at least this was quite hard and im not just rubbish! :-)

James.

Hilaire

Sounds odd ...
Five consecutive records have the same Account , Contract, Currency, Exchange
Normally they should collapse under the same group
4DH ALHG USD LME

It kindof ignores the group by statement in the main query
"derived
GROUP BY Account , Contract, Currency, Exchange"

Could you try the last version I posted ?

JAMES

ASKER

Well done!!!! The second version worked perfectly.

4DH ALHG USD LME 962817.43000000063 940317.43000000052 917817.43000000063 989817.43000000063 1012317.4300000006
4MM ALHG USD LME 1758796.5 1983796.5 2208796.5 1488796.5 1263796.5

I have to try and get my head around the way you have done it!!

Many thanks.