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.

JAMESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Could you post your code ?
0
muzzy2003Commented:
Almost certainly possible as a single SELECT if you post your code, possibly involving cases inside the aggregate functions.
0
JAMESAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JAMESAuthor Commented:
Sorry they should all read "FROM TicketsWithPricesAndProfitOrLoss" not "UpDownProfitOrLossByAccount".

I copied the wrong bit from SQL Analyzer.
0
HilaireCommented:
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
0
JAMESAuthor Commented:
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.
0
HilaireCommented:
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
0
JAMESAuthor Commented:
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.
0
HilaireCommented:
Sorry missing "Sum" (bad replace I guess)

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, Sum(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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JAMESAuthor Commented:
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.
0
HilaireCommented:
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 ?
0
JAMESAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.