CODEWITHSUNDEEP
mysqldategroup-bylimit
Peter Stuart
Peter Stuart

Reputation: 2444

Select where number of results is above x

I have a query like this:

SELECT 
  oh.date_added AS date_shipped, 
  o.date_added 
FROM 
  ".DB_PREFIX."order o , ".DB_PREFIX."order_history oh 
WHERE 
  o.order_id = oh.order_id 
  AND oh.order_status_id = 3

What I want to do is only show results if there are X or more rows with a particular date.

So if X=5 there are three entries from the 2013-04-01, then they won't show, but if there are nine entries (or anything >= 5) from 2013-04-02 then these results will be included in the query.

Once the requirements are met I would like the query to limit to that one group of rows from the matching date.

Does anyone know how I can achieve this?

Upvotes: 0

Views: 55

Answers (1)

sashkello
sashkello

Reputation: 17871

You can add nested SELECT to count these:

SELECT 
  oh.date_added AS date_shipped, 
  o.date_added 
FROM 
  ".DB_PREFIX."order o , ".DB_PREFIX."order_history oh 
WHERE 
  o.order_id = oh.order_id 
  AND oh.order_status_id = 3
  AND (SELECT COUNT(*) FROM ".DB_PREFIX."order o2 
              WHERE o2.date_added>"2013-04-01") > 5

Or join a table on itself, along the lines

SELECT 
  o.date,
  COUNT(*) cnt
FROM 
  order o
  JOIN order o2 ON o2.date = o.date AND o2.id != o.id
WHERE 
  your conditions
GROUP BY o2.id
HAVING cnt > 5

Upvotes: 2

Related Questions