Retreive all fields and count

[Ach]

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.

 

[RuralGuy]

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

 

[Ach]

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.

 

[Ach]

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.