"Fossies" - the Fresh Open Source Software Archive

Member "drizzle-7.1.36-stable/docs/having.rst" (6 May 2012, 2293 Bytes) of package /linux/misc/old/drizzle-7.1.36-stable.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format (assuming markdown format). Alternatively you can here view or download the uninterpreted source code file. A member file download can also be achieved by clicking within a package contents listing on the according byte size field.

HAVING

The WHERE keyword cannot be used with aggregate functions, but the HAVING clause can be; this is its primary use.

SQL HAVING Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

SQL HAVING Example

Take a look at the "Activities" table:

Id ActivityDate ActivityType ActivityCost userID
1 2011-01-02 Sport 45 131
2 2011-01-02 Art 10 256
3 2011-01-02 Music 25 022
4 2011-01-02 Food 125 022
5 2011-01-03 Music 40 131
6 2011-01-03 Food 20 175

In order to find if any users have spent more than $100 on recreational activities, use the following SQL statement:

SELECT userID,SUM(ActivityCost) FROM Activities
GROUP BY userID
HAVING SUM(ActivityCost)>100;

The result-set will look like this:

userID SUM(OrderPrice)
022 150

Now we want to find if userIDs "131", "256", or "175" spent less than $50 on Activities.

We add an ordinary WHERE clause to the SQL statement:

SELECT userID,SUM(ActivityCost) FROM Activities
WHERE userID='131' OR userID='256' OR userID="175"
GROUP BY userID
HAVING SUM(ActivityCost)<50;

The result-set would be:

userID SUM(OrderPrice)
256 10
175 20