Oracle SQL group by returns multiple rows

Question

Here is my table

And here comes my query (Oracle SQL) :

SELECT col_1, sum(col_2)
FROM table
GROUP BY col_1

I then expect to have as result :

But, outcome of this query is :

This is unexpected behavior from my understanding. Indeed, it seems that SQL consider col_3 to calculate result (A, X and A,Y combinations). But, to me, col_3 is not selected so it should not be taken into consideration to group by data.

I may have missed how to proceed with such cases.

Thanks in advance for your help to build up my knowledge :)

MT0 · Answer

You probably have a trailing whitespace character (or an zero-width character) in one row.

CREATE TABLE table_name (col_1, col_2, col_3) AS
SELECT 'A',  1, 'X' FROM DUAL UNION ALL
SELECT 'A ', 1, 'Y' FROM DUAL UNION ALL     -- Note the trailing space!
SELECT 'A',  2, 'X' FROM DUAL UNION ALL
SELECT 'B',  1, 'X' FROM DUAL UNION ALL
SELECT 'C',  1, 'X' FROM DUAL;

Then:

SELECT col_1, DUMP(col_1) FROM table_name;

Outputs:

COL_1	DUMP(COL_1)
A	Typ=1 Len=1: 65
A	Typ=1 Len=2: 65,32
A	Typ=1 Len=1: 65
B	Typ=1 Len=1: 66
C	Typ=1 Len=1: 67

You can see that the 2nd row has an extra character with the ASCII code 32 (which is a space character).

And:

SELECT col_1,
       sum(col_2)
FROM   table_name
GROUP BY col_1;

Outputs:

COL_1	SUM(COL_2)
`B`	1
`A`	1
`C`	1
`A`	3

Where there is, correctly, an A and an A group.

If you want to ignore the trailing space then:

SELECT RTRIM(col_1) AS col_1,
       sum(col_2)
FROM   table_name
GROUP BY RTRIM(col_1);

COL_1	SUM(COL_2)
B	1
C	1
A	4

Or else, UPDATE the table to remove the trailing spaces from col_1.

fiddle

Oracle SQL group by returns multiple rows

1 Answers