Oracle group by only ONE column

Question

I have a table in Oracle database, which have 40 columns. I know that if I want to do a group by query, all the columns in select must be in group by.

I simply just want to do:

select col1, col2, col3, col4, col5 from table group by col3

If I try:

select col1, col2, col3, col4, col5 from table group by col1, col2, col3, col4, col5

It does not give the required output.

I have searched this, but did not find any solution. All the queries that I found using some kind of Add() or count(*) function.

In Oracle is it not possible to simply group by one column ?

UPDATE:

My apologies, for not being clear enough.

My Table:

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 1      | 1        | some text 1 | 100   |
| 2      | 1        | some text 1 | 200   |
| 3      | 2        | some text 1 | 200   |
| 4      | 3        | some text 1 | 78    |
| 5      | 4        | some text 1 | 65    |
| 6      | 5        | some text 1 | 101   |
| 7      | 5        | some text 1 | 200   |
| 8      | 1        | some text 1 | 200   |
| 9      | 6        | some text 1 | 202   |
+--------+----------+-------------+-------+

and by running following query:

select col1, col2, col3 from table where col3='200' group by col1;

I will get the following desired Output:

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   |
| 3      | 2        | some text 1 | 200   |
| 7      | 5        | some text 1 | 200   |
+--------+----------+-------------+-------+

Solution 1

Long comment here;

Yeah, you can't do that. Think about it... If you have a table like so:

Col1 Col2 Col3
A    A    1
B    A    2
C    A    3

And you're grouping by only Col2, which will group down to a single row... what happens to Col1 and Col3? Both of those have 3 distinct row values. How is your DBMS supposed to display those?

Col1 Col2 Col3
A?   A    1?
B?        2?
C?        3?

This is why you have to group by all columns, or otherwise aggregate or concatenate them. (SUM(),MAX(), MIN(), etc..)

Show us how you want the results to look and I'm sure we can help you.

Edit - Answer:

First off, thanks for updating your question. Your query doesn't have id but your expected results do, so I will answer for each separately.

Without id

You will still need to group by all columns to achieve what you're going for. Let's walk through it.

If you run your query without any group by:

select col1, col2, col3 from table where col3='200'

You will get this back:

+----------+-------------+-------+
| col1     | col2        | col3  |
+----------+-------------+-------+
| 1        | some text 1 | 200   |
| 2        | some text 1 | 200   |
| 5        | some text 1 | 200   |
| 1        | some text 1 | 200   |
+----------+-------------+-------+

So now you want to only see the col1 = 1 row once. But to do so, you need to roll all of the columns up, so your DBMS knows what do to with each of them. If you try to group by only col1, you DBMS will through an error because you didn't tell it what to do with the extra data in col2 and col3:

select col1, col2, col3 from table where col3='200' group by col1 --Errors

+----------+-------------+-------+
| col1     | col2        | col3  |
+----------+-------------+-------+
| 1        | some text 1 | 200   |
| 2        | some text 1 | 200   |
| 5        | some text 1 | 200   |
| ?        | some text 1?| 200?  |
+----------+-------------+-------+

If you group by all 3, your DBMS knows to group together the entire rows (which is what you want), and will only display duplicate rows once:

select col1, col2, col3 from table where col3='200' group by col1, col2, col3

+----------+-------------+-------+
| col1     | col2        | col3  |
+----------+-------------+-------+
| 1        | some text 1 | 200   |
| 2        | some text 1 | 200   | --Desired results
| 5        | some text 1 | 200   |
+----------+-------------+-------+

With id

If you want to see id, you will have to tell your DBMS which id to display. Even if we group by all columns, you won't get your desired results, because the id column will make each row distinct (They will no longer group together):

select id, col1, col2, col3 from table where col3='200' group by id, col1, col2, col3

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   | --id = 2
| 3      | 2        | some text 1 | 200   |
| 7      | 5        | some text 1 | 200   |
| 8      | 1        | some text 1 | 200   | --id = 8
+--------+----------+-------------+-------+

So in order to group these rows, we need to explicitly say what to do with the ids. Based on your desired results, you want to choose id = 2, which is the minimum id, so let's use MIN():

select MIN(id), col1, col2, col3 from table where col3='200' group by col1, col2, col3
--Note, MIN() is an aggregate function, so id need not be in the group by

Which returns your desired results (with id):

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   |
| 3      | 2        | some text 1 | 200   |
| 7      | 5        | some text 1 | 200   |
+--------+----------+-------------+-------+

Final thought

Here were your two trouble rows:

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   |
| 8      | 1        | some text 1 | 200   |
+--------+----------+-------------+-------+

Any time you hit these, just think about what you want each column to do, one at a time. You will need to handle all columns any time you do grouping or aggregates.

• id, you only want to see id = 2, which is the MIN()
• co1, you only want to see distinct values, so GROUP BY
• col2, you only want to see distinct values, so GROUP BY
• col3, you only want to see distinct values, so GROUP BY

Solution 2

maybe analytic functions is what you need

try smth like this:

select col1, col2, col3, col4, col5 
, sum(*) over (partition by col1) as col1_summary
, count(*) over () as total_count
from t1 

if you google the article - you find thousands on examples for example this Introduction to Analytic Functions (Part 1)

Solution 3

Why do you want to GROUP BY , wouldn't you want to ORDER BY instead?

If you state an English language version of the problem you are trying to solve (i.e. the requirements) it would be easier to be more specific.

Comments

[pro_newbie]

I have update my question, can you please take a look at it

[Aaron Dietz]

@pro_newbie Updated my answer. This is conceptual stuff that everyone learns differently. I tried to explain it in a few different ways. Hopefully it helps.

[pro_newbie]

Thanks for explaining in detail. I have now much more understanding of it. One more thing, what if all the col2's values are also different from each other, then what you explained in Without id section would not work. It will give me all the records. How to handle that scenario ?

[Aaron Dietz]

@pro_newbie In that scenario you would have to decide how you want to handle col2. Your options would be 1.) Don't select it. 2.) Group by it, but accept the fact that the rows won't consolidate as much. (but yeah, in your hypothetical, they would not group at all...) 3.) Use an aggregate to show only the one you want (such as MIN(), as we did with id in the with id section)

[Aaron Dietz]

4.) Concatenate col2 values into a string, using LISTAGG() for example. I would only do this if you are pulling data... It is not a good way to store data. See: stackoverflow.com/questions/12145379/…

[Skocdopole]

Great answer! Went really step by step to the gist of the problem.