Hey all,

I'm having a hard time getting the output below from a query and was wondering if anyone would be able to reproduce this. Basically I'm trying to retrieve products that have more than 5 sales on a specific date and the retailers they sold at. I have the following simplified table.


Table name: SALES

PRODUCT RETAILER SALESDATE
---------------------------------------
PRODUCT1 COMPANY1 10/10/07
PRODUCT1 COMPANY2 10/10/07
PRODUCT2 COMPANY1 10/10/07


I am trying to find which products had more than two sales on the same date, and retrieve the product name, the retailer and the sales

I.E THE ABOVE TABLE WOULD PRODUCE


PRODUCT SALESDATE RETAILER
--------------------------------------
PRODUCT1 10/10/07 COMPANY1
PRODUCT1 10/10/07 COMPANY2



I've been able to only get the first two fields successfully by using


SELECT PRODUCT, SALESDATE
FROM SALES
GROUP BY SALESDATE, PRODUCT
HAVING (Count(PRODUCT)>=2);


MY problem is getting the retailers to appear also. Any help would be appreciated.

So what happens when you add the Retailer field to your Select statement?

Well, I can't execute the query. I get the "You tried to execute a query that inlcudes "RETAILERS" as part of an aggregate function". If I add it to group by then all fields are empty. I have a feeling I need some kind of a subquery here but I can't wrap my head around it.

Well I was able to get the right results with the following query:


SELECT Sales.Product, Sales.Salesdate, Sales.Retailer

FROM Sales,
(
SELECT Sales.Salesdate, Sales.Product
FROM Sales
GROUP BY Sales.Salesdate, Sales.Product
HAVING Count(Sales.Product)>=2
) as t1

WHERE (Sales.Product= t1.Product AND Sales.Salesdate = T1.Salesdate)

;


Well I'm learning slowly but I'm not really happy atm. I have a feeling there is a simpler way or a better way to do this. Any thoughts would be welcome.