Home » Developer & Programmer » Reports & Discoverer » UNION returns 2 records for Same ID (Oracle 12c)
UNION returns 2 records for Same ID [message #681418] Thu, 16 July 2020 01:03 Go to next message
gritmanish
Messages: 8
Registered: June 2011
Junior Member

Hello Friends,
I am facing an issue with combining 2 result sets. It returns 2 records for same ID. Only the measures are different. Below is the query.

SELECT ID,FIRST_NAME,LAST_NAME,SUM(AUM) AS "AUM",COUNT(CLIENTS) AS "CLIENTS",TO_NUMBER(NULL) AS "SALES" FROM TABLE1 WHERE ID=100 GROUP BY ID,FIRST_NAME,LAST_NAME
UNION
SELECT ID,FIRST_NAME,LAST_NAME,TO_NUMBER(NULL) AS "AUM",TO_NUMBER(NULL) AS "CLIENTS",SUM(AMOUNT) AS "SALES" FROM TABLE2 WHERE ID=100 GROUP BY ID,FIRST_NAME,LAST_NAME

AM i missing something in the query?

Thanks
Manish
Re: UNION returns 2 records for Same ID [message #681419 is a reply to message #681418] Thu, 16 July 2020 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what do you want?

Re: UNION returns 2 records for Same ID [message #681420 is a reply to message #681419] Thu, 16 July 2020 02:38 Go to previous messageGo to next message
gritmanish
Messages: 8
Registered: June 2011
Junior Member

I need single record with SUM(AUM), COUNT(CLIENTS) & SUM(AMOUNT). Is it possible?
Re: UNION returns 2 records for Same ID [message #681422 is a reply to message #681420] Thu, 16 July 2020 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select ID,FIRST_NAME,LAST_NAME,SUM(AUM) AS "AUM",COUNT(CLIENTS) AS "CLIENTS",SUM(AMOUNT) AS "SALES" 
from (
  SELECT ID,FIRST_NAME,LAST_NAME, AUM, CLIENTS, TO_NUMBER(NULL) AS "AMOUNT" 
  FROM TABLE1 
  WHERE ID=100 
  UNION all
  SELECT ID,FIRST_NAME,LAST_NAME,TO_NUMBER(NULL) AS "AUM",TO_NUMBER(NULL) AS "CLIENTS",AMOUNT
  FROM TABLE2 
  WHERE ID=100 
)
GROUP BY ID,FIRST_NAME,LAST_NAME
/
Please read How to use [code] tags and make your code easier to read.
Re: UNION returns 2 records for Same ID [message #681423 is a reply to message #681422] Thu, 16 July 2020 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

or:
SELECT ID,FIRST_NAME,LAST_NAME,SUM(AUM) AS "AUM",sum(CLIENTS) AS "CLIENTS",sum(sales) AS "SALES"
from (
  SELECT ID,FIRST_NAME,LAST_NAME,SUM(AUM) AS "AUM",COUNT(CLIENTS) AS "CLIENTS",TO_NUMBER(NULL) AS "SALES" 
  FROM TABLE1 
  WHERE ID=100 
  GROUP BY ID,FIRST_NAME,LAST_NAME
  UNION all
  SELECT ID,FIRST_NAME,LAST_NAME,TO_NUMBER(NULL) AS "AUM",TO_NUMBER(NULL) AS "CLIENTS",SUM(AMOUNT) AS "SALES" 
  FROM TABLE2 
  WHERE ID=100 
  GROUP BY ID,FIRST_NAME,LAST_NAME
)
GROUP BY ID,FIRST_NAME,LAST_NAME
/
icon14.gif  Re: UNION returns 2 records for Same ID [message #681424 is a reply to message #681422] Thu, 16 July 2020 05:29 Go to previous message
gritmanish
Messages: 8
Registered: June 2011
Junior Member

Hi Michel,

1st approach worked very well. Thank you.

Regards
Manish
Previous Topic: adding & before parameter name in data model
Next Topic: with Oracle Report Builder 10. how to print a frame only in last page at the end of the page
Goto Forum:
  


Current Time: Tue Nov 24 12:37:58 CST 2020