| Orcale: Missing Group By expression working [message #661512] |
Wed, 22 March 2017 06:14  |
 |
MikeBnDe
Messages: 2
Registered: March 2017
|
Junior Member |
|
|
Hello community,
i am beginner (so bear with me please 
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  |
Bill B
Messages: 1971
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 |
cookiemonster
Messages: 13920
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.
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.
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
