Home » SQL & PL/SQL » SQL & PL/SQL » Orcale: Missing Group By expression working (Oracle 12, Windows)
Orcale: Missing Group By expression working [message #661512] Wed, 22 March 2017 06:14 Go to next message
MikeBnDe
Messages: 2
Registered: March 2017
Junior Member
Hello community,

i am beginner (so bear with me please Wink
and even though i found some hints about my question in Google, i could not
find an exact answer of my question.

As i understand it, in general you have to include every column in an group by statement
that is not an aggregate colum in the select statement, for example

select a, max(b)
from table
group by a;

I coded something like

select a, max(b), case
when a in (1,2) then 1
when a in (3,4) then 2
when a in (5,6) then 3
else 4
end as c
group by a

This worked even though i did NOT put the 'case ... end' term in the group by statement.
(when i include the 'case ... end' in the group by i get the exact same result.)

The only thing i found in google is that you don't have to include constant values
in the group by statement, so obviously

select a, max(b), 5 as const

works without putting the 5 in group by.
But in my case the constant value is different for different values of a.

So, my question is:

1) Is my expression (without putting 'case ... end' in group by always 100% correct.
Or could there be a situation where it outputs an incorrect result?
(in my result, i only want one record for every possible value of column a)

2) If the answer is yes, then what is the exact rule according to the Oracle docs?


Thanks a lot,
Mike




Re: Orcale: Missing Group By expression working [message #661513 is a reply to message #661512] Wed, 22 March 2017 06:37 Go to previous messageGo to next message
Bill B
Messages: 1715
Registered: December 2004
Senior Member
the value it is testing is "a" and since you are already grouping on "a", there is no need to group by it a second time.

If your query looked like

select a, substr(a,1,3) junk,max(b), case
when a in (1,2) then 1
when a in (3,4) then 2
when a in (5,6) then 3
else 4
end as c
group by a,substr(a,1,3)

Then you would require the substr in the group by because you are grouping by a totally different value

[Updated on: Wed, 22 March 2017 06:39]

Report message to a moderator

Re: Orcale: Missing Group By expression working [message #661515 is a reply to message #661512] Wed, 22 March 2017 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't have to group by a function that modifies a column if you're grouping by that column.
Where this can get confusing is if you group by the column but only select a function e.g.
row generator query to get dates seperated by 12 hours (so pairs with same date/different time)
SQL> select sysdate + (rownum/2) as a, rownum as b from dual connect by level < 10;

A                        B
--------------- ----------
20170322 234210          1
20170323 114210          2
20170323 234210          3
20170324 114210          4
20170324 234210          5
20170325 114210          6
20170325 234210          7
20170326 114210          8
20170326 234210          9

9 rows selected.
Now lets select trunc(a) but group by a
SQL> with data as (select sysdate + (rownum/2) as a, rownum as b from dual connect by level < 10)
select trunc(a), max(b)
from data group by a
order by 1;  2    3    4

TRUNC(A)            MAX(B)
--------------- ----------
20170322 000000          1
20170323 000000          3
20170323 000000          2
20170324 000000          5
20170324 000000          4
20170325 000000          6
20170325 000000          7
20170326 000000          8
20170326 000000          9

9 rows selected.
We've got different groups with the same value for the 1st column, more likely we wanted this:
SQL> with data as (select sysdate + (rownum/2) as a, rownum as b from dual connect by level < 10)
select trunc(a), max(b)
from data group by trunc(a)
order by 1;  2    3    4

TRUNC(A)            MAX(B)
--------------- ----------
20170322 000000          1
20170323 000000          3
20170324 000000          5
20170325 000000          7
20170326 000000          9

And now there's one group per day
Re: Orcale: Missing Group By expression working [message #661516 is a reply to message #661515] Wed, 22 March 2017 06:49 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Adding the substr wouldn't require it be added to the group by - it's still a function that modifies a.
Re: Orcale: Missing Group By expression working [message #661517 is a reply to message #661512] Wed, 22 March 2017 07:07 Go to previous messageGo to next message
MikeBnDe
Messages: 2
Registered: March 2017
Junior Member
That was fast - thanks a lot, guys Smile

Re: Orcale: Missing Group By expression working [message #661530 is a reply to message #661517] Wed, 22 March 2017 10:56 Go to previous message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For your next question, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Previous Topic: copy table structure
Next Topic: CURSOR taking more time
Goto Forum:
  


Current Time: Tue Feb 20 02:15:13 CST 2018

Total time taken to generate the page: 0.02233 seconds