?
Solved

Merge results from a query

Posted on 2004-11-23
12
Medium Priority
?
358 Views
Last Modified: 2012-05-05
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.

0
Comment
Question by:JAMES
  • 6
  • 5
12 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12654676
Could you post your code ?
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12654738
Almost certainly possible as a single SELECT if you post your code, possibly involving cases inside the aggregate functions.
0
 

Author Comment

by:JAMES
ID: 12654814
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:JAMES
ID: 12654872
Sorry they should all read "FROM TicketsWithPricesAndProfitOrLoss" not "UpDownProfitOrLossByAccount".

I copied the wrong bit from SQL Analyzer.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12654941
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
 

Author Comment

by:JAMES
ID: 12654976
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12655103
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
 

Author Comment

by:JAMES
ID: 12655126
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 12655138
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
 

Author Comment

by:JAMES
ID: 12655155
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12655248
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
 

Author Comment

by:JAMES
ID: 12655293
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question