LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 11-04-2004, 07:32 AM   #1
jdruin
Member
 
Registered: Jul 2003
Location: Louisville aka Derby City
Distribution: WinXP SP2 and SP3, W2K Server, Ubuntu
Posts: 313

Rep: Reputation: 30
Question: How to define an SQL HAVING clause in relational algebra?


I noticed an SQL query I ran produced different results depending on whether I used a WHERE clause or a HAVING clause. I believe the WHERE clause is defined in relational algebra as a predicate that must return true for the record (tuple) to be returned to the user. I am not sure but I have always thought of the predicate as being in the form of a symbolic logic syllogism.

Based on what the HAVING clause does (filter), it seems that it would be expressed in relational algebra as a predicate as well. However, when a WHERE clause is used rather than a HAVING clause, the resulting row set (tuples) can be quite different.

Do you know how to express HAVING in relational algebra in such a way that the relational algebra description will be unambiguous from the WHERE clause definition? I would like to know why WHERE and HAVING behave differently.

Example:

Problem statement: Select only negative values from field some_number, sum those negative values, then group the sums by period.

Returns the "expected" results
SELECT period, SUM(some_number)
FROM some_table
WHERE some_number < 0
GROUP BY period

Using HAVING causes the SQL interpreter engine to select and SUM the records before considering the HAVING clause, resulting in positive numbers being added together with the negative numbers in the result set. The result is then returned as long and the total sum of number is negative.

SELECT period, SUM(some_number)
FROM some_table
GROUP BY period
HAVING some_number < 0
 
Old 11-08-2004, 07:50 AM   #2
rjlee
Senior Member
 
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,990

Rep: Reputation: 66
Not in relational algebra (I probably could sit down and work it out but I don't have time at the moment). See http://www.postgresql.org/docs/7.4/s...ql-select.html for a formal definition of HAVING, WHERE and other SQL clauses.

It basically comes down to when you apply the filter, before aggregation (WHERE) or after (HAVING). The relevant paragraph is:
Quote:
HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
C++ question #define ... Bluesuperman Programming 4 02-01-2005 09:49 AM
Algebra and family tree software? Slovak Linux - Software 0 11-30-2004 05:46 PM
question on #define h/w Programming 7 12-03-2003 05:14 PM
Boolean Algebra question gdbugger General 5 10-23-2003 06:54 PM
php, sql and query with a lot of or's in where clause markus1982 Programming 1 09-26-2003 12:27 PM


All times are GMT -5. The time now is 08:46 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration