Sql Having Sum Group By

Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to “check out” equipment. When he selects a product, I need to

Solution 1:

You can have an inner select, such as:

SELECT count_of_foo, count(bar), baz
FROM (SELECTcount(foo) as count_of_foo, bar, baz, other1, other2 FROM complex_query WHERE foo = bar HAVINGcount(foo) >1) inner_query
GROUPBY count_of_foo, baz.

This will give you the ability to add more group by after the HAVING clause.

Solution 2:

What you are trying to do is a running sum, which you can get with various techniques in SQL. I think the most efficient query, especially if you are trying to do this all in the same query, is to use a CTE (here's one example).

Another technique that doesn't rely on CTE requires the data to be populated into another table (could be a temp table, though) and basically you do a join-and-sort operation as you go.

Once you get the data to include a running sum, then you can simply select the values from which the running sum is less than or equal to the total number that you are trying to locate.

And here is a nice summary of several of the different techniques.