Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shilpesh
Starting Member
18 Posts |
 Posted - 2001-12-21 : 05:10:22
|
| How do I handle this:create table t1 ( id int, crit1 int, crit2 int, qty int)In each group of (crit1, crit2) I want to find a subset of rows where sum(qty) = 0 if such a subset exists, given that no more than one subset would exist for each group. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-21 : 09:57:13
|
| SELECT crit1, crit2 FROM t1 GROUP BY crit1, crit2 HAVING Sum(qty)=0HAVING is equivalent to WHERE, but it also works on aggregate functions, and WHERE does not. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-21 : 12:12:46
|
The HAVING clause is applied AFTER the grouping occurs. The WHERE clause is applied BEFORE the grouping occurs. You can have both a HAVING and a WHERE clause in the same select statement. Rob, perhaps this is what you meant, but I didn't read it this way.  -------------------It's a SQL thing... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-21 : 12:34:08
|
Yes, that is what I meant  Why is it so hard to explain things that you understand perfectly? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-21 : 12:49:53
|
quote:
Why is it so hard to explain things that you understand perfectly?
Having taught for several years, I can tell you that THAT is one of the big things that separates a good teacher from a bad one. There is an art to teaching. Of course another thing that separates them is that some bad teachers just don't understand their topic either. But we'll cut you some slack since it's the holidays...  <edit rant>And yes, the reason I left teaching is because the pay SUCKS! It's really depressing to have your students go get jobs that pay more than you make. I make 4X the income as a doer that I made as a teacher.</edit rant>-------------------It's a SQL thing...Edited by - AjarnMark on 12/21/2001 12:52:17 |
|
|
|
shilpesh
Starting Member
18 Posts |
Posted - 2002-01-24 : 07:32:54
|
| Sorry for the late reply but having clause will work on the whole set of rows condensed by the 'group by' clause whereas i want to find any existing subset. |
|
|
|
fisherman_jake
Slave to the Almighty Yak
159 Posts |
Posted - 2002-01-24 : 07:42:23
|
shilpesh,Are you talking about ISOLATED sets of rows within the whole set.ie:crit1 crit2 qty (set through group by)----- ----- --- -----1 1 0 1st1 1 0 1st2 2 1 2nd2 3 2 3rd1 1 0 1stSo you are talking about the first 2 rows, and does not include the 5th row?? Is that what you were talking about?? ==================================================World War III is imminent, you know what that means... No Bag limits!!!Master Fisherman |
|
|
|
|
|
|
|
|
Permutations