
Archives of the TeradataForumMessage Posted: Fri, 17 Jun 2005 @ 10:37:51 GMT
Because "having" and "where" provide different capabilities. Consider the following Select c1, sum(c2) From tbl Where c3 = 4 Group by 1; Select c1, sum (c2) From tbl Having sum (c2) = 4 Group by 1; In the first query, the where clause controls which records are selected for summation. The second query sums all records but only returns those records whose sum is 4. The data is C1 c2 c3 A 2 4 * A 2 0 B 2 4 * B 1 4 * c1 Sum(c2) A 2 B 3 The first query produces the above because it selects records marked with the * and sums up the c2 values. Note that B is 3 (2 + 1) and A is 2 (because the where clause excluded the second row for A). The second query produces the following: c1 Sum(c2) A 4 If you think of the query without the having clause, you will not it will add up all the values (i.e A > 4 and b > 3), but the having clause excludes sums that are not = 4, so B (whose sum is just 3) is eliminated from the results. If you modify the query to this: Select c1, sum(c2) From tbl Where c3 = 4 Having sum (c2) = 4 Group by 1; You should get nothing back. Why, because the where clause excludes the row without the asterisk. Now the A sum is only 2, the B sum is still 3, neither of which is = 4, so an empty result set is generated. Try creating a table for the above insert the data and run these tests for yourself. I used sum, but the principle is the same for max and all the other aggregation functions. I'll leave it to you to work out a "MAX" example. Good Luck
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 15 Jun 2023  