This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 12, 2013 7:45 AM by BluShadow RSS

count function

pawii Newbie
Currently Being Moderated
how to count the number of employee hired in various years
  • 1. Re: count function
    jeneesh Guru
    Currently Being Moderated
    Select extract(year from hire_date) yr,count(*) cnt
    From employees
    Group by extract(year from hire_date)
    Order by 1
  • 2. Re: count function
    pawii Newbie
    Currently Being Moderated
    u r printing the years in rows actually i want the years as column names like 1995 1996 ......nd the no. of employees hired must be below them
  • 3. Re: count function
    ---Brodyaga--- Explorer
    Currently Being Moderated
    select *
    from (Select extract(year from hire_date) yr,count(*) cnt
          From employees
          Group by extract(year from hire_date)
          Order by 1)
    pivot (max(cnt) for yr in (2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008))
  • 4. Re: count function
    ranit B Expert
    Currently Being Moderated
    987018 wrote:
    u r printing the years in rows actually i want the years as column names like 1995 1996 ......nd the no. of employees hired must be below them
    What actually you asking for seems to be like PIVOTING

    Try this... not tested

    Assuming* - Your database version >11g
    PIVOT won't work in databases <11g... we need to implement the SUM(DECODE()) logic to get the same result.
    select *
    from
    (Select extract(year from hire_date) yr,count(*) cnt
    From employees
    Group by extract(year from hire_date)
    Order by 1)
    PIVOT(
     SUM(cnt) 
       FOR yr IN (1995, 1996,...) --"specify the years here that you need"
    )
    Please tell us your database version.
    select *
    from v$version;
  • 5. Re: count function
    jeneesh Guru
    Currently Being Moderated
    987018 wrote:
    u r printing the years in rows actually i want the years as column names like 1995 1996 ......nd the no. of employees hired must be below them
    This is called pivoting. And since the number of columns is not pre-defined, you will have to do dynamic pivoting..

    Almost all the methods to do pivoting is available in the FAQ - {message:id=9360005}

    Check that, specifically Dynamic Pivoting
  • 6. Re: count function
    jeneesh Guru
    Currently Being Moderated
    ranit B wrote:
    select *
    from
    (Select extract(year from hire_date) yr,count(*) cnt
    From employees
    Group by extract(year from hire_date)
    Order by 1)
    PIVOT(
    SUM(cnt) 
    FOR yr IN (1995, 1996,...) --"specify the years here that you need"
    )
    FYI: The ORDER BY here is of NO use..
  • 7. Re: count function
    pawii Newbie
    Currently Being Moderated
    actually i had to do this by only using the count function not by using subqueries
  • 8. Re: count function
    jeneesh Guru
    Currently Being Moderated
    987018 wrote:
    actually i had to do this by only using the count function not by using subqueries
    WHY?

    What is the problem in using the subquery?


    I want to drive the car - but don't want to use the accelerator ....
  • 9. Re: count function
    pawii Newbie
    Currently Being Moderated
    actually i dont want to drive a car nd if i want to drive i will still wont use the accelarator because i dont know how to drive a car

    nd it was not about driving a car its about getting the work done through count function only nd tht need intelligence not accelarator :)
    nd m new to sql so using accelarator now may cause some accidents etc :)
  • 10. Re: count function
    RajeshKanna Newbie
    Currently Being Moderated
    hey,
    by using the decode function u can get the result

    SELECT sum(decode(extract(year from hiredate),1980,1,0)) "1980",
               sum(decode(extract(year from hiredate),1981,1,0)) "1981"
    FROM emp;
  • 11. Re: count function
    jeneesh Guru
    Currently Being Moderated
    987018 wrote:
    actually i dont want to drive a car nd if i want to drive i will still wont use the accelarator because i dont know how to drive a car

    nd it was not about driving a car its about getting the work done through count function only nd tht need intelligence not accelarator :)
    nd m new to sql so using accelarator now may cause some accidents etc :)
    Still you did not answer the basic question- Why cant you use subqueries?
  • 12. Re: count function
    pawii Newbie
    Currently Being Moderated
    my sir has told me to do it by using basic count function only by the way i had brought out the same output by using a simple query thn yours

    select to_char(hire_date,'year'),count(hire_date)
    from employees
    group by to_char(hire_date,'year')

    if u want to see the results
    log in with the hr account nd run this query its more simple thn urs xtract

    nd the basic functions are very useful dude we can do anything by using them that why i am asked to focus now on basics only :)
    hope this query will help u :)
    m a newbie to oracle
  • 13. Re: count function
    pawii Newbie
    Currently Being Moderated
    u r xctly correcr rajesh but tell me dude whats the use of 1,0 after 1985
    nd can u please tell me step by stem the procedure
  • 14. Re: count function
    BluShadow Guru Moderator
    Currently Being Moderated
    987018 wrote:
    u r xctly correcr rajesh but tell me dude whats the use of 1,0 after 1985
    nd can u please tell me step by stem the procedure
    It seems that some keys on your keyboard are broken as you are failing to form words correctly. Perhaps you could ask your hardware guys to provide you with a new keyboard.

    You could easily break down the query given to understand what the 1 and 0 are for... it allows a count of records to be made by using the sum function where 1 is a matching record and 0 is a non-matching record for that column.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points