?
Solved

Grouping an Union query [Oracle]

Posted on 2010-01-11
6
Medium Priority
?
562 Views
Last Modified: 2013-12-18
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
Comment
Question by:aletheia1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26281731
Howis ur sample output should look like?
0
 
LVL 6

Accepted Solution

by:
magedroshdy earned 668 total points
ID: 26281735
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
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 668 total points
ID: 26281758
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
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.

 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 664 total points
ID: 26281773
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
 
LVL 41

Expert Comment

by:Sharath
ID: 26281792
provide your expected output.
0
 

Author Comment

by:aletheia1
ID: 26282152
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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

752 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