11 Replies Latest reply: Nov 19, 2012 7:16 AM by 930854 RSS

    Count operations

    930854
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Thanks for your reply

                        It's working fine now. Thank yo
                        • 9. Re: Count operations
                          jeneesh
                          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
                            Glad that it helped... Thanks!

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