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   |
+--------+----------+-------------+-------+

Answer 1

SELECT * FROM table 
WHERE id IN (SELECT MIN(id) FROM table WHERE col3='200' GROUP BY col1)

Answer 2

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

Answer 3

I guess,maybe you need upivot function

or post your specific final result you want

select  col3, col_group 
from table
UNPIVOT ( col_group for value in ( col1,col2,col4,col5)) 

Answer 4

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.

Answer 5

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)