1 2 Previous Next 16 Replies Latest reply: Feb 12, 2013 9:45 AM by BluShadow RSS

    count function

    pawii
      how to count the number of employee hired in various years
        • 1. Re: count function
          jeneesh
          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
            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---
              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
                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
                  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
                    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
                      actually i had to do this by only using the count function not by using subqueries
                      • 8. Re: count function
                        jeneesh
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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