This discussion is archived
11 Replies Latest reply: Nov 19, 2012 5:16 AM by 930854 RSS

Count operations

930854 Newbie
Currently Being Moderated
Hi,

I have a table called employees

name Hire_date
machiel 01-02-05
karthi 05-06-06
jp 07-08-05
mak 08-02-06
xavi 23-04-08


i want to display the total no of employees name and the total no of employees hired_date like

Total 2005 2006 2008
5 2 2 1


please help me to get this answer...
  • 1. Re: Count operations
    Stew Ashton Expert
    Currently Being Moderated
    You have asked 28 questions and all of them are "unresolved".

    That means you never marked any of your questions as "answered": not one.

    Why do you ask questions here if they are never answered?

    If you think some of your questions were answered, please get in the habit of marking them so. Feedback encourages people to keep trying to help.
  • 2. Re: Count operations
    Hoek Guru
    Currently Being Moderated
    Please take Stew's suggestions into serious consideration.
    It is all explained in the topics you'll find if you read the SQL and PL/SQL FAQ.
    See: {message:id=9360002} and {message:id=9360005}

    As for your (PIVOT) query, try this and see if it works for you:
    SQL> -- generating sample data:
    SQL> with t as (
      2  select 'machiel' name,  to_date('01-02-05', 'dd-mm-yy') hire_date from dual uni
      3  select 'karthi', to_date('05-06-06', 'dd-mm-yy') from dual union
      4  select 'jp', to_date('07-08-05', 'dd-mm-yy') from dual union
      5  select 'bmak', to_date('08-02-06', 'dd-mm-yy') from dual union
      6  select 'xavi', to_date('23-04-08', 'dd-mm-yy') from dual
      7  )
      8  --
      9  -- actual query:
     10  --
     11  select count(*) total
     12  ,      sum(case 
     13           when to_char(hire_date, 'yyyy') = '2005' then count(*)
     14         end) "2005"    
     15  ,      sum(case
     16           when to_char(hire_date, 'yyyy') = '2006' then count(*)
     17         end) "2006"
     18  ,      sum(case
     19           when to_char(hire_date, 'yyyy') = '2008' then count(*)
     20         end) "2008"
     21  from   t
     22  group by hire_date;
    
         TOTAL       2005       2006       2008
    ---------- ---------- ---------- ----------
             5          2          2          1
    I used the WITH-clause to create a 'fake table' callled T. (It's explained in the FAQ)
    Adjust as necessary.
  • 3. Re: Count operations
    ranit B Expert
    Currently Being Moderated
    Try if this helps-
    with xx as(
         select 'machiel' name,to_date('01-02-05','dd-mm-yy') hired from dual union all
         select 'karthi' name,to_date('05-06-06','dd-mm-yy') from dual union all
         select 'jp' name,to_date('07-08-05','dd-mm-yy') from dual union all
         select 'mak' name,to_date('08-02-06','dd-mm-yy') from dual union all
         select 'xavi' name,to_date('23-04-08','dd-mm-yy') from dual 
    )
    select count(*) total,
      sum(case when extract(year from hired) = '2005'
        then count(*) end) as "2005",
      sum(case when extract(year from hired) = '2006'
        then count(*) end) as "2006",
      sum(case when extract(year from hired) = '2008'
        then count(*) end) as "2008"
    from xx
    group by hired;
  • 4. Re: Count operations
    930854 Newbie
    Currently Being Moderated
    sorry for the late reply ...
    am new in this forum, i dono how to marking it. Please tel me how to mark it.
  • 5. Re: Count operations
    jeneesh Guru
    Currently Being Moderated
    Or Pivot in 11g. (Only if the number columns is predefined).
    Normally, when you are using hire_date column, there is no chance that you can assume the number of years that will come as columns. In that case, you will have to do dynamic pivoting - whcih is available in FAQ link already shared
    with t as
     (  
        select nvl(to_char(hire_date,'yyyy'),'TOTAL') yr,count(*) cnt
        from employees
        where to_char(hire_date,'yyyy') in ('2005','2006','2008')
        group by rollup(to_char(hire_date,'yyyy'))
     )
    select *
    from t
     pivot
     (
       sum(cnt) for yr in ('TOTAL','2005','2006','2008')
     );
    
    'TOTAL' '2005' '2006' '2008'
    ------- ------ ------ ------
          5      2      2      1 
  • 6. Re: Count operations
    Solomon Yakobson Guru
    Currently Being Moderated
    It is a good habit to write code in such way optimizer can use index even if index currently doesn't exits:
    with xx as(
         select 'machiel' name,to_date('01-02-05','dd-mm-yy') hired from dual union all
         select 'karthi' name,to_date('05-06-06','dd-mm-yy') from dual union all
         select 'jp' name,to_date('07-08-05','dd-mm-yy') from dual union all
         select 'mak' name,to_date('08-02-06','dd-mm-yy') from dual union all
         select 'xavi' name,to_date('23-04-08','dd-mm-yy') from dual 
    )
    select count(*) total,
      sum(case when hired >= date '2005-01-01' and hired < date '2006-01-01'
        then count(*) end) as "2005",
      sum(case when  hired >= date '2006-01-01' and hired < date '2007-01-01'
        then count(*) end) as "2006",
      sum(case when  hired >= date '2008-01-01' and hired < date '2009-01-01'
        then count(*) end) as "2008"
    from xx
    group by hired
    where hired >= date '2005-01-01' and hired < date '2009-01-01';
    SY.
  • 7. Re: Count operations
    stratmo Newbie
    Currently Being Moderated
    Hi,

    probably a ROLLUP.
    select nvl(to_char(hire_date_year),'TOTAL'), anz_name from (
    with hlp as(
    select 'machiel' name, to_date('01-02-05', 'DD-MM-YY') Hire_date from dual
    union all
    select 'karthi' name, to_date('05-06-06', 'DD-MM-YY') Hire_date from dual
    union all
    select 'jp' name, to_date('07-08-05', 'DD-MM-YY') Hire_date from dual
    union all
    select 'mak' name, to_date('08-02-06', 'DD-MM-YY') Hire_date from dual
    union all
    select 'xavi' name, to_date('23-04-08', 'DD-MM-YY') Hire_date from dual)
    select extract( year from hlp.hire_date) as hire_date_year, count(hlp.name) anz_name 
    from hlp hlp
    group by rollup (extract(year from hlp.hire_date)));
    Problem: This solution has to be pivoted. to get your result.

    Regards

    stratmo
  • 8. Re: Count operations
    930854 Newbie
    Currently Being Moderated
    Thanks for your reply

    It's working fine now. Thank yo
  • 9. Re: Count operations
    jeneesh Guru
    Currently Being Moderated
    927851 wrote:
    sorry for the late reply ...
    am new in this forum, i dono how to marking it. Please tel me how to mark it.
    You will see the link to mark the question as answered at top left of your first post.

    and you will see, "helpful" and "correct" button on top of each reply...
  • 10. Re: Count operations
    ranit B Expert
    Currently Being Moderated
    Glad that it helped... Thanks!

    Please keep in mind what Jeneesh told and mark the questions answered.
    Ranit B.
  • 11. Re: Count operations
    930854 Newbie
    Currently Being Moderated
    Well I understand for my question, I got the answer..

Legend

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