CODEWITHSUNDEEP
sqlms-access
jazza1000
jazza1000

Reputation: 4247

SQL find which products appear every week

I have some queries that return lists of products for a time period, and I want to find out which products appear in all of those time periods.

WeekEnding  | Product
  07/07/14  | A
  07/07/14  | B
  07/07/14  | C
  14/07/14  | A
  14/07/14  | B
  21/07/14  | A
  21/07/14  | B
  21/07/14  | C

So in the above example data I would have products A, and B which are in all 3 weeks, and I could imagine running a query like

SELECT Product FROM ProductWeek
GROUP BY Product
HAVING COUNT(*) = (
SELECT COUNT(distinct weekending) from ProductWeek )

Unfortunately I am writing this query in MSAccess, so count distinct is not available, but at any rate it feels like there should be a more elegant solution to this problem

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can do it with a subquery:

SELECT Product
FROM ProductWeek
GROUP BY Product
HAVING COUNT(*) = (SELECT COUNT(*) from (SELECT distinct weekending from ProductWeek ) as t);

Upvotes: 1

Related Questions