SQL select all records only if the sum is greater than 100

If I understand your request, you want similar results to what you've already got, but filtering for those names who have met the quota. If that is correct, you can use an IN() subquery to find names grouped with >= 100 widgets.

SELET name, widgets, TransDate, Location FROM MyTable WHERE  /* IN() subquery for names meeting the quota */  name IN (     SELECT name      FROM tbl      /* If they must have met the quota only during the time window, uncomment below */     /* Otherwise, omit the WHERE clause to find those who have met the quota at any time */     /* WHERE TransDate BETWEEN '1/1/2011' and '12/31/2011' */     GROUP BY name      HAVING SUM(widgets) >= 100  )   AND TransDate BETWEEN '1/1/2011' and '12/31/2011'

for sql server it could be done like this

SELECT m.name, m.widgets, m.TransDate, m.Location FROM MyTable mJOIN(SELECT name, SUM(widgets)             FROM  MyTable             WHERE TransDate BETWEEN '1/1/2011' and '12/31/2011'            GROUP BY NAME             HAVING SUM(widgets) >= 16) xON x.NAME = m.NAMEWHERE m.TransDate BETWEEN '1/1/2011' and '12/31/2011'

For SQL Server 2005+ you could also try:

SELECT name, widgets, TransDate, LocationFROM (       SELECT name, widgets, TransDate, Location, SUM(widgets) OVER(PARTITION BY Name) Quant       FROM MyTable       WHERE TransDate BETWEEN 1/1/2011 and 12/31/2011) AWHERE Quant >= 16

This is assuming that the quota must be meeted on the same date frame.