This discussion is archived
11 Replies Latest reply: Aug 6, 2012 3:39 PM by SomeoneElse RSS

SQL error

952526 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    GROUP BY must be after the WHERE clause.
  • 3. Re: SQL error
    Hoek Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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')

Legend

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