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

Question

I'm not quite sure how to ask about this, so I will give an example

I have a huge table that resembles something like this ...

Name Widgets TransDate Location Abby 2 12/1/2010 Middleton Abby 13 1/10/2011 Burmingham Bobby 10 12/12/2011 Easton Bobby 5 10/10/2011 Weston . . 

And my current sql statement ...

 SELECT name, widgets, TransDate, Location FROM MyTable WHERE TransDate BETWEEN 1/1/2011 and 12/31/2011 

to give me a table like this ...

 Name Widgets TransDate Location Abby 13 1/10/2011 Burmingham Bobby 15 12/12/2011 Easton Bobby 5 10/10/2011 Weston . . 

How to change the above SQL to also get rid of people records that do not match the widget quota X ... let's say X = 16. In this case, Abby will be dropped because her total number of widgets is 13 and Bobby’s entries will remain because he the total amount is 20.

Thank you in advance!

Answer 1

If I understand your request, you want to get similar results with what you already have, but filtering for those names that match the quota. If this is correct, you can use the IN() subquery to find the names grouped by s> = 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' 

Answer 2

for sql server it can be done like this:

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

Answer 3

For SQL Server 2005+, you can also try:

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

This suggests that the quota should be met on the same date frame.