• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Grouping an Union query [Oracle]

Hi all,
I'm working on a query that looks (more or less) like this:

SELECT
category, software, user_PC
FROM categories, softwares, users
GROUP BY category, software, user
UNION ALL
SELECT
category, software, PC
FROM categories, softwares, PCs
GROUP BY category, software, PC

but what I get as result is something like:

Graphic - Photoshop - John Smith
Graphic - Photoshop - Jack Russel
Microsoft - Office Word - Usain Bolt
Microsoft - Office Excel - Mister Mint
Graphic - Photoshop - Additional_PC
Graphic - Corel Draw - Unknown_PC

As you can see, the first and the second part of the query are grouped separately (first, all of users, and then, all of PCs). But what I hope to get is the group "Graphic" for both users and PCs, then the group Microsoft, and so on...

How can I do it?
Any help is really appreciated. Thank you in advance!
0
aletheia1
Asked:
aletheia1
3 Solutions
 
Shaju KumbalathDeputy General Manager - ITCommented:
Howis ur sample output should look like?
0
 
magedroshdyCommented:
try the following
select all_results.* from (
SELECT
category, software, user_PC
FROM categories, softwares, users
GROUP BY category, software, user
UNION ALL
SELECT
category, software, PC
FROM categories, softwares, PCs
GROUP BY category, software, PC
) all_results
GROUP BY category

Open in new window

0
 
Shaju KumbalathDeputy General Manager - ITCommented:
are u looking for
SELECT
category, software, user_PC
FROM categories, softwares, users
GROUP BY category, software, user
UNION ALL
SELECT
category, software, PC
FROM categories, softwares, PCs
GROUP BY category, software, PC
order by  category, software
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MilleniumaireCommented:
It sounds like it is just the order you are concerned with.  This can be changed as follows:

SELECT
category, software, user_PC
FROM categories, softwares, users
GROUP BY category, software, user
UNION ALL
SELECT
category, software, PC
FROM categories, softwares, PCs
GROUP BY category, software, PC
ORDER BY 1,2,3
0
 
SharathData EngineerCommented:
provide your expected output.
0
 
aletheia1Author Commented:
Sorry guys, the problem was caused by LAG / LEAD functions that I included in the query, I forgot to mention it.

So I finally used a nested query (as Magedroshdy suggested), and applied those functions in the outer query.

Thanks to everybody anyway!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now