Search code examples
sqlaggregate

SQL-select two values that are the same in different rows but still be able to see a third value

i have three columns orderdate, customerid and productid. i only want to see the rows where orderdate and customerid is repeated. so when the customer orders something on the same day. but i also want to see the product id.

SELECT orderdate, customerid, productid
FROM sales_orders_sheet
GROUP BY orderdate, customerid
HAVING COUNT(*)>1

when I use this sql statement I get;

Field of aggregated query neither grouped nor aggregated: line 1, column 31

Solution

  • You can do:

    select *
from (
  select t.*, count(1) over(partition by orderdate, customerid) as cnt
  from sales_orders_sheet t
) x
where cnt > 1