Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY FUNCTION (11i,9i,WINDOWs)
GROUP BY FUNCTION [message #422017] Sun, 13 September 2009 09:04 Go to next message
shaaquille
Messages: 2
Registered: September 2009
Junior Member
hi
i am newbie and i am preparing for oracle sql exam...
i am having problems with a query ... hope u kan help me Smile
------------------------------------------
create a query that displays total number of employees...
and of that total number,number of emoloyees hired in 1980,1981,1982
out put shd be like this

total -- 1980 -- 1981 -- 1982 -- 1983
14 ----- 2------ 1 ----- 3 ----- 3

----------------------------------------------
thank you in advance
regards !
Re: GROUP BY FUNCTION [message #422019 is a reply to message #422017] Sun, 13 September 2009 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is a FAQ asked every week.
Please search BEFORE posting.
In this case search for "pivot" or "row to columns".

Please read OraFAQ Forum Guide and follow it.

Regards
Michel
Re: GROUP BY FUNCTION [message #422046 is a reply to message #422017] Mon, 14 September 2009 01:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL>  select HIREYEAR,count
  2   from
  3   (
  4   select to_char(HIREDATE,'yyyy ') HIREYEAR,count(0) count
  5   from scott.emp
  6   where to_char(HIREDATE,'yyyy ') in (1981,1982,1987)
  7   group by to_char(HIREDATE,'yyyy ')
  8  )
  9   MODEL   
 10   DIMENSION BY (HIREYEAR)
 11   MEASURES (count)
 12    RULES (
 13    count['total'] = sum(count)[HIREYEAR IN (1981,1982,1987)] )
 14  /

HIREY      COUNT
----- ----------
1982           1
1981          10
1987           2
total         13

[Updated on: Mon, 14 September 2009 01:56]

Report message to a moderator

Re: GROUP BY FUNCTION [message #422054 is a reply to message #422046] Mon, 14 September 2009 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't think this what is asked and in your case ROLLUP should be a better option than MODEL.

Regards
Michel
Re: GROUP BY FUNCTION [message #422074 is a reply to message #422017] Mon, 14 September 2009 03:56 Go to previous messageGo to next message
shaaquille
Messages: 2
Registered: September 2009
Junior Member
thanx fr all of your replies !
actually i had solved it... and it was quite simple fr me :s
-----------------------
select count(ename)total,
sum(count(case to_char(hiredate,'YYYY') when '1980' then 1 end)) "1980",
sum(count(case to_char(hiredate,'YYYY') when '1981' then 1 end)) "1981",
sum(count(case to_char(hiredate,'YYYY') when '1982' then 1 end)) "1982",
sum(count(case to_char(hiredate,'YYYY') when '1983' then 1 end)) "1983"
from emp
group by ename
---------------------------
it worked fr me !
once again thanx fr your replies !
regards !
Re: GROUP BY FUNCTION [message #422086 is a reply to message #422074] Mon, 14 September 2009 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Better use "extract" function than "to_char" one:
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
                    2009

1 row selected.

Regards
Michel
Re: GROUP BY FUNCTION [message #422168 is a reply to message #422017] Mon, 14 September 2009 23:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
neat, aint seen that before.

Kevin
Re: GROUP BY FUNCTION [message #422207 is a reply to message #422054] Tue, 15 September 2009 02:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
thanks Michel

I was just trying to use MODEL somewhere
while Rollup is very good really
SQL> SELECT   DECODE (GROUPING (EXTRACT (YEAR FROM hiredate)),
  2                   1, 'TOTAL',
  3                   EXTRACT (YEAR FROM hiredate)
  4                  ) hireyear,
  5           COUNT (0) cou
  6      FROM scott.emp
  7     WHERE TO_CHAR (hiredate, 'yyyy') IN (1981, 1982, 1987)
  8  GROUP BY ROLLUP (EXTRACT (YEAR FROM hiredate))
  9  /

HIREYEAR                                        COU
---------------------------------------- ----------
1981                                             10
1982                                              1
1987                                              2
TOTAL                                            13
Re: GROUP BY FUNCTION [message #422209 is a reply to message #422086] Tue, 15 September 2009 02:07 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Michel Cadot wrote on Mon, 14 September 2009 12:50
Better use "extract" function than "to_char" one:
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
                    2009

1 row selected.

Regards
Michel
Michel, out of curiosity: what's "better" about an extract? I have used it once or twice, because (if I'm not mistaken) you get a numeric result but in my experience it can confuse fellow developers. It is a bit more obscure than to_char.

In short: the only advantage I can see is that you get a NUMBER instead of a VARCHAR2. Is that what you meant or am I missing something?

MHE
Re: GROUP BY FUNCTION [message #422213 is a reply to message #422209] Tue, 15 September 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Michel, out of curiosity: what's "better" about an extract?

Just 2 points:
1/ This is what you want: extracting year from the date (that is get a number) and not displaying a year (which is the purpose to to_char)
2/ The implementation.
Date is in format (not exactly that but the principle), century/year in century/month/day/hour/minute/second, each field in a byte.
When using extract, Oracle knows exactly which byte(s) to extract and directly goes to it (them).
When using to_char, Oracle gets the whole date and call a function that interprets the format and for each format element get the corresponding byte(s) in the date, converts it (them), put it in the result string and goes to the next part of given format.

Regards
Michel
Re: GROUP BY FUNCTION [message #422214 is a reply to message #422213] Tue, 15 September 2009 02:29 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Thanks for that. So extract might perform better than to_char, am I correct? As for your first point: I agree but like I said: it can also confuse people. I don't know why, I even think 'extract' explains itself quite nicely, but I had people on my desk asking me about this mysterious function I have been using.

I'll need to investigate the performance of both. Thanks again.

MHE
Re: GROUP BY FUNCTION [message #422219 is a reply to message #422214] Tue, 15 September 2009 02:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I just did some tests (code below, and Extract does seem to be faster than TO_CHAR. For year conversions, it's about 15-20% faster.
create or replace type ty_timing_table as table of number;
/

declare
  
  t_timing    ty_timing_table := ty_timing_table();
  
  v_iter      pls_integer := 10000;
  v_max       pls_integer := 100;
  v_time      pls_integer;
  v_avg       number;
  v_total     pls_integer;
  v_stddev    number;
  v_num       number;
  
begin
  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    for i in 1..v_max loop
      v_num := extract(year from sysdate-v_max*5);
    end loop;
    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;
  
  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 1: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));
  
  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    for i in 1..v_max loop
      v_num := to_char(sysdate-v_max*5,'yyyy');
    end loop;

    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;
  
  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 1: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));  
  
end;

Re: GROUP BY FUNCTION [message #422220 is a reply to message #422219] Tue, 15 September 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the test case.
I got:
Test 1: Total: 139 Avg: .0139 stddev: .16
Test 1: Total: 163 Avg: .0163 stddev: .166


Yes, this was my 2 points that "extract" is self-explained and faster.
I agree with you that some people find it more complex, maybe because there are more letters or maybe they just are not used to use it yet.

Regards
Michel
Re: GROUP BY FUNCTION [message #422229 is a reply to message #422220] Tue, 15 September 2009 03:24 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Very Happy Hey, that's what a forum is all about: try to share the knowledge and pick up some new things.

Thanks both for the clarifications!

MHE

[Updated on: Tue, 15 September 2009 03:26]

Report message to a moderator

Re: GROUP BY FUNCTION [message #422329 is a reply to message #422017] Tue, 15 September 2009 08:54 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
maybe because there are more letters


Hehe, I get this a lot.

Kevin
Previous Topic: Stored Procedure in oracle to select and update table data.
Next Topic: Privilege problem
Goto Forum:
  


Current Time: Sun Dec 08 06:00:55 CST 2024