Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: WHERE COUNT(EVENT)..........not working

    Hi guys, here is a simple code i need to debug but struggling:

    Code:
    select person, purchase from shopdb
    where count(purchase) > 3
    error: misuse of aggregate or OLAP function

    I'm simplifying the code because it seems I am misunderstanding how to use the count here.

    also this doesn't work

    Code:
    select person, count(purchase) as purchcount from shopdb
    where purchcount > 3
    in which case i get purchcount is invalid in this context.

    I just want to get a count of how many purchases a person made and only bring back people who have made over 3 purchases.

    Any suggestions would be great!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT person
         , COUNT(purchase) AS purchcount 
      FROM shopdb
    GROUP
        BY person  
    HAVING purchcount > 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    33
    Thanks - completely forgot about the HAVING clause. solved.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Just remember that the predicates in the WHERE clause are evaluated for each row individually. Thus, trying to aggregate something there wouldn't work due to this single row context.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •