1 2 Previous Next 26 Replies Latest reply on Feb 14, 2013 9:17 PM by 276532

    help about this query

    Goldray
      Hi all,
      I want to create a query that displays the total number of employees and number of employees whose hire date is 2000 ...

      //total number of employees
      select count(*) from EMP;

      //hire date =2000

      select count(*) from emp
      group by hiredate
      having(hiredate like'%2000') ;

      but how can I collect the two query?

      note:
      Emp(_empno_,ename,hiredate,deptno)
        • 1. Re: help about this query
          Frank Kulash
          Hi,
          979155 wrote:
          Hi all,
          I want to create a query that displays the total number of employees and number of employees whose hire date is 2000 ...

          //total number of employees
          select count(*) from EMP;

          //hire date =2000

          select count(*) from emp
          group by hiredate
          having(hiredate like'%2000') ;
          Is hiredate a string?
          That's a very bad idea. Date information belongs in DATE columns, not in strings.
          but how can I collect the two query?
          Do you want one row of output, regardless of what is in the table?
          Here's one way:
          SELECT  COUNT (*)  AS total_employees
          ,       COUNT (CASE WHEN hiredate LIKE '%2000' THEN 1 END)   
                             AS hired_in_2000
          FROM    emp;
          note:
          Emp(_empno_,ename,hiredate,deptno)
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: help about this query
            636309
            979155 wrote:
            Hi all,
            I want to create a query that displays the total number of employees and number of employees whose hire date is 2000 ...

            //total number of employees
            select count(*) from EMP;

            //hire date =2000

            select count(*) from emp
            group by hiredate
            having(hiredate like'%2000') ;

            but how can I collect the two query?

            note:
            Emp(_empno_,ename,hiredate,deptno)
            Hi, how about something like this:
            select a.total_count, b.2000_count
            from 
            (select count(*) as total_count from emp) a, 
            (select count(*) as 2000_count from emp where hiredate >= to_date('1/1/2000', 'MM/DD/YYYY')) b
            • 3. Re: help about this query
              Goldray
              hiredate is a date :)
              • 4. Re: help about this query
                Frank Kulash
                Hi,
                979155 wrote:
                hiredate is a date :)
                The operands to LIKE are strings. Don't use a DATE where a string is expected.

                Maybe this is what you want:
                SELECT  COUNT (*)  AS total_employees
                ,       COUNT ( CASE 
                                 WHEN  hiredate >= DATE '2000-01-01'
                              AND       hiredate <  DATE '2001-01-01'
                                    THEN  1 
                          END
                          )  AS hired_in_2000
                FROM    emp;
                Please post some sample data, and the results you want from that data.
                • 5. Re: help about this query
                  Goldray
                  so I must to use this function :
                  substr(hiredate,7,4) ?
                  • 6. Re: help about this query
                    Frank Kulash
                    Hi,
                    979155 wrote:
                    so I must to use this function :
                    substr(hiredate,7,4) ?
                    No, you must NOT do that.
                    The 1st argument to SUBSTR is supposed to be a string. Do not use a DATE where a string is expected.
                    • 7. Re: help about this query
                      Goldray
                      thank u ,its works :)
                      i have some questions :
                      What is the role of "case" ? its like "Switch case " ?
                      why you put this:hiredate >= DATE '2000-01-01' ? we can replace this:hiredate >= '2000-01-01' //eliminating DATE
                      • 8. Re: help about this query
                        Frank Kulash
                        Hi,
                        979155 wrote:
                        thank u ,its works :)
                        i have some questions :
                        What is the role of "case" ? its like "Switch case " ?
                        Sorry, I don't know what "Switch case" is.
                        If you're not familiar with CASE, look it up in the SQL Language manual
                        In this example, CASE is returning a non-NULL value if (and only if) hiredate is in the year 2000, so the COUNT will increase by 1 every time a row with a hiredate in 2000 is encountered.
                        why you put this:hiredate >= DATE '2000-01-01' ? we can replace this:hiredate >= '2000-01-01' //eliminating DATE
                        Not if you want the right results.
                        The expressions on both sides of the >= operator are supposed to be the same type.
                        hiredate is a DATE.
                        <tt> DATE '2000-01-01' </tt> is a DATE, so it's okay to compare it to hiredate using the >= operator.
                        <tt> '2000-01-01' </tt> is a VARCHAR2, so it's wrong to try to compare it to a DATE such as hiredate.
                        • 9. Re: help about this query
                          Goldray
                          its not work
                          • 10. Re: help about this query
                            Goldray
                            Not if you want the right results.
                            The expressions on both sides of the >= operator are supposed to be the same type.
                            hiredate is a DATE.
                            <tt> DATE '2000-01-01' </tt> is a DATE, so it's okay to compare it to hiredate using the >= operator.
                            <tt> '2000-01-01' </tt> is a VARCHAR2, so it's wrong to try to compare it to a DATE such as hiredate.
                            so DATE allows conversion ?
                            • 11. Re: help about this query
                              276532
                              If I am correct that the second count is for all employees hired only in the year 2000, not just all employees hired since the year 2000, then try this:

                              SELECT all_emp.emp_count AS all_emp_count,
                              emp_2000.emp_count AS emps_hired_2000
                              FROM (SELECT count(*) AS emp_count FROM emp) all_emp,
                              (SELECT count(*) AS emp_count
                              FROM emp
                              WHERE to_char(hiredate,'YYYY') = '2000') emp_2000;

                              You can do this too:

                              SELECT all_emp.emp_count AS all_emp_count,
                              emp_2000.emp_count AS emps_hired_2000
                              FROM (SELECT count(*) AS emp_count FROM emp) all_emp,
                              (SELECT count(*) AS emp_count
                              FROM emp
                              WHERE hiredate < to_date('01-JAN-2001')
                              AND hiredate >= to_date('01-JAN-2000')) emp_2000;
                              1 person found this helpful
                              • 12. Re: help about this query
                                Frank Kulash
                                Hi,
                                979155 wrote:
                                Not if you want the right results.
                                The expressions on both sides of the >= operator are supposed to be the same type.
                                hiredate is a DATE.
                                <tt> DATE '2000-01-01' </tt> is a DATE, so it's okay to compare it to hiredate using the >= operator.
                                <tt> '2000-01-01' </tt> is a VARCHAR2, so it's wrong to try to compare it to a DATE such as hiredate.
                                so DATE allows conversion ?
                                Sorry, it's unclear what you're asking. Do you want to know why
                                <tt> DATE '2000-01-01' </tt> is a DATE, but
                                <tt> '2000-01-01' </tt> is a VARCHAR2?

                                Text encosed by single-quotes is some kind of literal.
                                If the keyword DATE appears immediatly before the text in single-quotes, then it's a DATE literal.
                                If the keyword INTERVAL appears immediatly before the text in single-quotes, then it's an INTERVAL literal.
                                If the keyword TIMESTAMP appears immediatly before the text in single-quotes, then it's a TIMESTAMP literal.
                                If none of those keywords appears immediatly before the text in single-quotes, then it's a VARCHAR2 literal.
                                • 13. Re: help about this query
                                  Goldray
                                  Your first method works but not the 2nd
                                  • 14. Re: help about this query
                                    276532
                                    Didn't work as in "didn't provide the results expected" or as in "Syntax error"? (I don't have the emp tables installed in my database.)
                                    1 2 Previous Next