2

Can anyone please help me figure this out, I have 3 tables: Customer, Products and Products_ordered and I am trying to find customers who have ordered more than 1 product. Here is my query:

SELECT customer_id, product_id
FROM product_ordered
GROUP BY customer_id
HAVING COUNT (customer_id)>1;

I am getting this error: Error report: SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"

Thanks for helping

user25830
  • 23
  • 1
  • 1
  • 5

3 Answers3

2

try

select customer_id, product_id from product_ordered group by customer_id,product_id having count (customer_id)>1;
sunysen
  • 2,265
  • 1
  • 12
  • 13
1

Try:

SELECT customer_id
FROM product_ordered
GROUP BY customer_id
HAVING COUNT (customer_id)>1;

The issue is that product_id is not part of the group by. If you do a group by, you can only select columns in the group by or use an aggregate function. That query will return the customer_id's that occur more then once. I don't know your table structure, but if you want more data then just the id let us know what sql version you are using, SQL Sever, MYSQL, or Oracle, and I can try to write something with windowing functions.

Vulcronos
  • 3,428
  • 3
  • 16
  • 24
  • Thanks,I am using Oracle SQL. But i figured it out. i used a subquery in conjunction with the IN clause. select customer_id, product_id from product_ordered where customer_id in (select customer_id from product_ordered group by customer_id having count (customer_id) > 1) ; – user25830 Sep 26 '13 at 02:49
0

Don't you really want to select Customers who ordered more than one product?

More than one order line, or more than one product, or more than one unique product?

If you run as an inline query

(select customer_id from product_ordered group by customer_id having count (customer_id) > 1)

You will see all customers who placed more than one order line. But there could be multiple lines in an order, or multiple orders of one line, yada yada...

Try select customer_id from product_ordered group by customer_id having count(distinct product_id)>1 which will let you actually see customers who bought more than one unique product.

chrlsuk
  • 56
  • 3