Re: [GENERAL] Problem with aggregates and group by

Sebestyn Zoltn AKA Memphisto wrote:

> select annex_log.username,
> annex_log.login_time,annex_log.bytes_in, annex_log.bytes_out
> from users, groups, annex_log
> where
> ppp_start >= date_trunc('day','1998 September 20'::datetime) and
> ppp_start < (date_trunc('day','1998 September 20'::datetime) +
> '1 day'::timespan)
> group by username
>
> and found out that that query returned the very same lines a lot of
> times. It can be a side effect of join.
> So I included a 'distinct' in the second query and it finally gave me the
> correct lines, but how can I to that when using aggregates, too?

When you join two tables, the database program makes pairs of *each*
row from the first table with *every* row from the second. Normally
you will specify a condition (e.g. "where table1.xxx = table2.yyy") to
limit the output to those rows of the result table, where two key
columns have equal values. You did some kind of a join between the
three tables by specifying them in the "from" phrase, but you did not
give the advise how to join them in the "where" clause. The database
now joins really every row with all the others ... BTW, why do you
join the three tables in this query? You are querying columns from the
annex_log-table only ;-)

Unfortunately, I can't recommend a good book about SQL since I have
read only the user manuals of the commercial database systems I had to
work with; I know a rather theoretical textbook (Database System
Concepts, H. F. Korth & A. Silberschatz, McGraw-Hill 1991) which
explaines a bit of SQL (shortly!) and gives a (theoretical!)
introduction of relational operations like joins. Maybe you find it in
a library where you can copy the interesting pages ... But I'm sure
there are people reading the list who can give better tips ;-)

Hope it helps,
Ulf

--
======================================================================
%%%%% Ulf Mehlig <ulf(dot)mehlig(at)uni-bremen(dot)de>
%%%%!%%% Projekt "MADAM" <umehlig(at)uni-bremen(dot)de>
%%%% %!% %%%% ----------------------------------------------------
---| %%% MADAM: MAngrove | Center for Tropical Marine
||--%!% Dynamics | Biology
|| And | Fahrenheitstrasse 1
_ /||\_/\_ Management |
/ / \ \ ~~~~~~~~~~~~~~~~~ | 28359 Bremen/Germany
~~~~~~~~~~~~~~~~~~~~