11 Replies Latest reply: Aug 6, 2012 5:39 PM by SomeoneElse RSS

    SQL error

    952526
      HI

      I am trying to do the following

      select count(create_time) from comm_voice_extra group by create_time where count(create_time)> 1;

      it gives
      ORA-00933: SQL command not properly ended

      however

      if I just run

      select count(create_time) from comm_voice_extra group by create_time ;

      then its OK, so what is wrong if I add "where count(create_time)> 1;"
        • 1. Re: SQL error
          Frank Kulash
          Hi,

          You probably want a HAVING cluse, not a WHERE clause:
          SELECT    COUNT (create_time) 
          FROM        comm_voice_extra 
          GROUP BY  create_time 
          HAVING        COUNT (create_time)> 1
          ;
          The difference between WHERE and HAVING is that WHERE is applied before aggregate functions are computed, and HAVING is applied after aggregate fucntions are computed.
          If you have both a WHERE clause and a GROUP BY clause, then the WHERE clause has to come first. That seems to be what's causing the error.
          However, if you fix that, you'll still get an error, because you're using an aggregate function (COUNT) in the WHERE clause, but the WHERE calsue has to be applied before aggregate functions are computed.

          Edited by: Frank Kulash on Aug 6, 2012 5:11 PM

          In case you're interested:
          There's not much flexibility in the order of clauses. Whenever the following clauses are used, they must come in this order:
          SELECT
          FROM
          WHERE
          CONNECT BY
          GROUP BY
          ORDER BY

          The SQL language manual for Oracle 11.2
          http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#sthref6812
          says that HAVING can come either immediately before or immediately after GROUP BY. Earlier versions said that GROUP BY had to come first, but actaully allowed either.
          • 2. Re: SQL error
            Matthew Morris
            GROUP BY must be after the WHERE clause.
            • 3. Re: SQL error
              Hoek
              Predicates (WHERE-clause) go first, so try:
              select count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;
              (HAVING and GROUP BY can be interchanged in order)
              • 4. Re: SQL error
                Khayyam
                You need to use HAVING keyword to operate with agregate functions...
                select count(create_time) 
                from comm_voice_extra 
                group by create_time 
                having count(create_time)> 1
                • 5. Re: SQL error
                  Apostolis
                  Hi,

                  2 things to notice here:
                  1) it's common practice to use the WHERE clause before GROUP BY
                  2) do not use aggregate functions in the WHERE clause. If you want to filter rows before grouping them, then use a WHERE clause. If you want to exclude certain groups of the generated aggregated groups, use HAVING. You can use an aggregate function in the HAVING clause.

                  Having said that, it seems that you are looking for something like the following:

                  SELECT COUNT(CREATE_TIME)
                  FROM comm_voice_extra
                  GROUP BY CREATE_TIME
                  HAVING COUNT(CREATE_TIME)> 1;

                  Regards,
                  Apostolis
                  • 6. Re: SQL error
                    952526
                    yes, Having is correct one

                    I use

                    select count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;
                    now I get the following

                    count (create-time) 2,

                    if I want to see the data


                    the following seems not working .......
                    select *,count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;

                    it gives ORA-00923: FROM keyword not found where expected..........
                    • 7. Re: SQL error
                      Apostolis
                      Hi,

                      Again 2 things:
                      1) You keep using aggregate functions in the WHERE clause. It will never work.
                      2) You cannot use SELECT * in an aggregate query. You should use the same columns that you use in GROUP BY. In your case, only SELECT CREATED_TIME.

                      Regards,
                      Apostolis
                      • 8. Re: SQL error
                        Frank Kulash
                        Hi,
                        949523 wrote:
                        yes, Having is correct one

                        I use

                        select count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;
                        now I get the following

                        count (create-time) 2,

                        if I want to see the data


                        the following seems not working .......
                        select *,count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;

                        it gives ORA-00923: FROM keyword not found where expected.........
                        Remember the ABC's of GROUP BY:
                        When you use a GROUP BY clause and/or an aggregate fucntion, then every item in the SELECT clause must be:
                        (A) an <b>A</b>ggregate function,
                        (B) one of the "group <b>B</b>y" expressions,
                        (C) a <b>C</b>onstant, or
                        (D) something that <b>D</b>epends entirely on the above. (For example, if you "GROUP BY TRUNC(dt)", you can "SELECT TO_CHAR (TRUNC(dt), 'Mon-DD')").

                        Since you're only GROUPing BY create_time, you can't use any other columns from the table in the SELECT clause, unless they are the arguments of aggregate functions.

                        Also, you're still using COUNT in the WHERE clause.

                        Perhaps you want to use the analytic COUNT, not the aggregate COUNT. For example:
                        WITH     got_cnt          AS
                        (
                             SELECT    comm_extra_vocie.*
                             ,       COUNT (create_time) OVER (PARTITION BY  create_time)     AS total_cnt
                             FROM        comm_voice_extra 
                        )
                        SELECT     *
                        FROM     got_cnt
                        WHERE     total_cnt     > 1
                        ;
                         

                        I hope this answers your question.
                        If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, 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.
                        See the forum FAQ {message:id=9360002}
                        • 9. Re: SQL error
                          952526
                          Thank you very much, now I retirieve like the following

                          7/10/2012 11:17:29 AM

                          here I wnat to learn something

                          if I do comparison

                          can I use create_time='/7/10/2012 11:17:29 AM' in query

                          I find out it does not work for the following

                          create_time like '%11:17:29%' in query
                          • 10. Re: SQL error
                            SomeoneElse
                            can I use create_time='/7/10/2012 11:17:29 AM' in query
                            It's not a good idea to do this.

                            If your create_time column is a DATE type, then the other side of the = sign should also be a date type.

                            where create_time = to_date(...)
                            • 11. Re: SQL error
                              Khayyam
                              If create_time column is date format, then use to_date function
                              create_time=to_date('7/10/2012 11:17:29 AM','DD/MM/YYYY HH:MI:SS AM')