Re: HAVING and WHERE clause

From: Rob Medley <medleyrk_at_iafrica.com>
Date: Wed, 06 Jan 1999 00:38:59 +0200
Message-ID: <36929482.E45B09AB@iafrica.com>

Greetings...

Maybe the following will help clear up your confusion :

Consider a simple table like the following :

SQL> desc food

 Name                            Null?    Type
 ------------------------------- -------- ----
 TYPE                                     VARCHAR2(10)
 NAME                                   VARCHAR2(10)
 COST                                     NUMBER(7,2)

And data in the table as follows :

SQL> select * from food;

TYPE            NAME        COST
----------      ----------     ----------
FRUIT              APPLE           1.45
FRUIT              PEACH            2.5
FRUIT              GRAPE            .99
VEGETABLE   CORN             1.1
VEGETABLE   PEAS               .77

VEGETABLE CARROTS 1.89 6 rows selected.

If we wanted to get the total price of the food, broken into the fruit and vegetable groups, we could use the following query :

SQL> select type, sum(cost) from food group by type;

TYPE                    SUM(COST)
----------               ---------
FRUIT                   4.94
VEGETABLE        3.76

So far so good. Your question is how do we exclude rows from a group calculation ? In our case the group function is SUM. What if we wanted the same query as above, but wanted to exclude grapes from the SUM ? You have to use a WHERE clause :

SQL> select type, sum(cost) from food where name <> 'GRAPE' group by type;

TYPE                SUM(COST)
----------          ---------
FRUIT               3.95

VEGETABLE 3.76 Notice the price of the fruit is now lower.

Once the query has done all of the work and calculated the sums for you, you can weed out rows from your result set using the HAVING clause. In our case :

SQL> select type, sum(cost) from food where name <> 'GRAPE' group by type

           having type = 'FRUIT';

TYPE SUM(COST)
---------- ---------
FRUIT 3.95 Good luck.

Rob Medley
medleyrk_at_iafrica.com

> I read this question from somewhere. I am confused as I think the
> correct answer should be 2. But the correct answer is 1.
>
> Which clause would you use to exclude rows from a group calculation?
>
> 1. WHERE
> 2. HAVING
> 3. ORDER BY
> 4. INTO
>
> Will HAVING is for a group of rows and WHERE is for one row?
>
> --
> Best regards,
Received on Tue Jan 05 1999 - 16:38:59 CST