Archives of the TeradataForum

Message Posted: Fri, 17 Jun 2005 @ 10:37:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>