Home » Other » General » Just Pondering (all)
Just Pondering [message #320945] Fri, 16 May 2008 17:11 Go to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
So I was sitting at work after a few pints at the pub.

I was creating a load of group by statements, and it hit me, why does Oracle need the GROUP BY statement?

select col1, col2, count(1)
from table
group by col1, col2;


Surely Oracle could work out the group by clause. Its always the same as the select clause without the aggregate functions isnt it??

Anyway, just a thought. Any comments?
Re: Just Pondering [message #320963 is a reply to message #320945] Sat, 17 May 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the syntax. Full stop.

Regards
Michel
Re: Just Pondering [message #321003 is a reply to message #320945] Sat, 17 May 2008 12:22 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
You are missing the point. which it is why its a "general" topic, and not a question in the main topics.

I know its the syntax. But its certainly not the full stop. Syntaxes evolve, thats why we have generations of programming languages, and we are not all writing 1's and 0's.

For example, "decode" was the syntax until someone decided that a case statement would be better. Someone came up with the idea of analytic functions because they didnt want to use group by to get an aggregate.

Some people like to look forward for improvements, not backwards because thats the way its always been done.

The question was just a ponderance on whether the oracle SQL parsing engine could do without it, or whether there is a magic case that means you couldnt work it out (if you were parsing the query).

Re: Just Pondering [message #321004 is a reply to message #320945] Sat, 17 May 2008 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Take it up with ANSI/ISO who are the ones which maintain the SQL language standard.
Re: Just Pondering [message #321005 is a reply to message #321003] Sat, 17 May 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
YOU miss the point of what is a standard language.
SQL syntax is driven by standard.
"group by" is part of the standard, Oracle follows the standard (when it is defined) and so there is "group by". Full stop. No RDBMS will implement grouping without "group by".

DECODE is NOT part of the standard, it is Oracle proprietary.
CASE is in standard, but was not in the first standard, this is why Oracle introduce DECODE until CASE was defined.

By the way, "group by" is part of SQL syntax, DECODE is a function, functions are not part of the standard, each RDBMS can implement the ones they want.

Regards
Michel
Re: Just Pondering [message #321011 is a reply to message #320945] Sat, 17 May 2008 15:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
coleing wrote on Fri, 16 May 2008 15:11
So I was sitting at work after a few pints at the pub.

I was creating a load of group by statements, and it hit me, why does Oracle need the GROUP BY statement?

select col1, col2, count(1)
from table
group by col1, col2;


Surely Oracle could work out the group by clause. Its always the same as the select clause without the aggregate functions isnt it??

Anyway, just a thought. Any comments?


What sort of place do you work where you can have a few pints at the pub (at lunch?) then return to work? Is this standard practice in other countries? Most American businesses expert you to be completely sober while at work.

Interesting ponderings. I have had only caffeinated beverages, but it makes sense to me. It would be a nice enhancement and save a little coding and decrease the likelihood for miscoding if Oracle would just figure the group by clause for you and execute the select statement without it, as if it had been included. However, it would not be a simple task to include that in the parsing. Let us assume that it already divides each select statement and sub-select and so forth into the select clause, from clause, where clause, group by clause, and order by clause. So, it should be a matter of extracting the appropriate columns from the select clause and creating a group by clause from them.

There are exceptions, such as analytic functions. You could have a COUNT (...) OVER (PARTITION BY ... ORDER BY ...) without a group by clause. I started to play with it just a little and it would have to do something like the following for each select statement. I only included two aggregate functions and assumed that the aggregate function would be at the end of the columns list. Obviously, this is just a start and there would be a lot more that would need to be done.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION add_group_by
  2    (p_sql	   IN VARCHAR2)
  3    RETURN	      VARCHAR2
  4  AS
  5    v_select       VARCHAR2 (32767);
  6    v_from	      VARCHAR2 (32767);
  7    v_order_by     VARCHAR2 (32767);
  8    v_group_by     VARCHAR2 (32767);
  9    v_sql	      VARCHAR2 (32767);
 10  BEGIN
 11    v_select := SUBSTR (p_sql, 1, INSTR (UPPER (p_sql), ' FROM ') - 1);
 12    v_from := SUBSTR (p_sql, INSTR (UPPER (p_sql), ' FROM '));
 13    IF INSTR (UPPER (v_from), ' ORDER BY ') > 0 THEN
 14  	 v_order_by := SUBSTR (v_from, INSTR (UPPER (v_from), ' ORDER BY '));
 15  	 v_from := SUBSTR (v_from, 1, INSTR (UPPER (v_from), ' ORDER BY '));
 16    END IF;
 17    IF (INSTR (UPPER (v_select), 'COUNT') > 0 OR INSTR (UPPER (v_select), 'SUM') > 0)
 18  	  AND INSTR (UPPER (v_select), ' OVER ') = 0 THEN
 19  	 v_group_by := SUBSTR (v_select, 8);
 20  	 IF INSTR (UPPER (v_group_by), 'COUNT') > 0 THEN
 21  	   v_group_by := ' GROUP BY ' || SUBSTR (v_group_by, 1, INSTR (UPPER (v_group_by), 'COUNT') - 1);
 22  	 ELSIF INSTR (UPPER (v_group_by), 'SUM') > 0 THEN
 23  	   v_group_by := ' GROUP BY ' || SUBSTR (v_group_by, 1, INSTR (UPPER (v_group_by), 'SUM') - 1);
 24  	 END IF;
 25  	 v_group_by := RTRIM (v_group_by, ', ');
 26    END IF;
 27    v_sql := v_select || v_from || v_group_by || v_order_by;
 28    RETURN v_sql;
 29  END add_group_by;
 30  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT add_group_by ('SELECT deptno, job, COUNT (*) FROM emp WHERE 1 = 1 ORDER BY deptno, job') FROM DUAL
  2  /

ADD_GROUP_BY('SELECTDEPTNO,JOB,COUNT(*)FROMEMPWHERE1=1ORDERBYDEPTNO,JOB')
----------------------------------------------------------------------------------------------------
SELECT deptno, job, COUNT (*) FROM emp WHERE 1 = 1  GROUP BY deptno, job ORDER BY deptno, job

SCOTT@orcl_11g> 



I don't know what the proper route is to submit such a suggestion as either an ANSI SQL enhancement or Oracle enhancement, but if you are really interested you might pursue it.


Re: Just Pondering [message #321016 is a reply to message #320945] Sat, 17 May 2008 17:48 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
"What sort of place do you work where you can have a few pints at the pub (at lunch?) then return to work?"

lol. Its the UK. pubs are very busy on friday lunchtimes over here. Safe to say that in some companies over here also it is not acceptable. Although in quite a lot of places it is encouraged for team building.
Anyway, I could have meant pints of water Smile

Many thanks for your responses.

Barbara, I think you have proved it could be done with that function. I know its not all singing or all dancing, but the pricipal is there.

For the record, im not on a crusade to get the standard changed, im just wondering why you would put a "group by" clause in a standard if it was not actually required. I was seeing if I was missing some reason where you really need it or you cant work out the logic of the query without it. Considering some the sql ive seen and just how insanely complex it can get, I think working out the group by clause would be within the realms of possibility.

And Michael, Just because soemthing is defined in a "standard", doesnt mean we cant call it into question does it? Thats where updated standards come from.
You did get me on the decode point though Wink

Ive just been reading through the ANSI-92 syntax, and the <group by> and something I dont quite understand:-

         3) For every grouping column, if <collate clause> is specified,
            then the data type of the <column reference> shall be character
            string. The column descriptor of the corresponding column in the
            result has the collating sequence specified in <collate clause>
            and the coercibility attribute Explicit.


http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Whats a <collate clause> in reference to a group by clause?

[Updated on: Sat, 17 May 2008 17:51]

Report message to a moderator

Re: Just Pondering [message #321219 is a reply to message #321016] Mon, 19 May 2008 08:15 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Um, aren't we forgetting something?
select max(sal)
from   emp
group by mgr

Must be all that light beer we dring in 10oz glass down here in Oz. Wink
Re: Just Pondering [message #321295 is a reply to message #320945] Mon, 19 May 2008 16:20 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
I knew it must have a point to it, otherwise why would it be there, I just couldnt figure out why. Thanks.

Although whether you actually ever get useful output from a query like that is debatable.

But it got me to realise this would work to prove its needed also:-

select col1, col2, col3
from tab1
group by col1, col2, col3
having count(*) > 1;


++ EDIT
Although looking at it again, the having clause could imply the group by in that case.

[Updated on: Mon, 19 May 2008 16:24]

Report message to a moderator

Previous Topic: Query status
Next Topic: Iam getting ERROR
Goto Forum:
  


Current Time: Thu Mar 28 18:28:33 CDT 2024