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  |
 |
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 #681422 is a reply to message #681420] |
Thu, 16 July 2020 03:56  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
/
|
|
|
|
| Re: UNION returns 2 records for Same ID [message #681423 is a reply to message #681422] |
Thu, 16 July 2020 04:08 |
|
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
/ |
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 22:39:43 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
