Count function in my where statement

[txwylde]

I am trying to create a query where I am bringing back order_date, ip_address and email_address. I only want those records where the count of the ip_address is greater than 2.
Here is my code.

select
count(ip_address)
order_date,
email,
ip_address
from risk.fraud_orders
where sales_district = "IN02"
and order_date >= "2005-01-01"
and count(ip_address) > 2;

I get an illegal group by function. What do I need to do to fix this code?
Thanks
Bill

 

[txwylde]

I figured it out.

select
count(ip_address) as cnt,
order_date,
email,
ip_address
from risk.fraud_orders
where order_date >= "2005-01-01"
group by ip_address
having cnt > 2;


I had my having in the wrong spot.

 

[swampBoogie]

When mixing columns and aggregate functions in the select list you need to use a group by clause. Also, you must use a having clause in order to specify restrictions for aggregate functions, e.g.

select
  count(*),
  order_date,
  email,
  ip_address
from risk.fraud_orders
where sales_district = 'IN02'
      and order_date >= '2005-01-01'
group by order_date,
  email,
  ip_address
having count(*) > 2

Since your query is so malformed it is not clear what result you are expecting. Give some sample data and the expected result.