I've three tables:
groups(name text); -- This one contains name of usergroups
users (username text, groupid oid); -- This one contains name users,
-- groupid is a foreign key to "groups" table's oid.
In the following table, the username attribute is the same as in "users"
so "annex_log" is in 1-n relation with "users"
annex_log (
port int2 not null,
login_start datetime, login_end datetime,
ppp_start datetime, ppp_end datetime,
login_time interval,
dialback_flag bool not null,
ready_flag bool not null,
bytes_in int4 not null,
bytes_out int4 not null,
username text not null,
tel_no text);
I'd like to summarize the users' login_time bytes_in and bytes_out
within a time interval.
I did it with the following query:
select annex_log.username,
sum(annex_log.login_time),sum(annex_log.bytes_in),
sum(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
I realized that it's not OK, it gave impossible results.
Then I removed the aggregate functions:
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?
P.S.: I realized I'm not an SQL wizard, could you suggest me a good
SQL book, please? I mean a book that trains me to solve problems like that
one above and not some 'Easy-to-use SQL' or 'SQL for beginners'.
Thanks in advance
--------------------------------------------------------------------------------
Sebestyén Zoltán AKA Memphisto It all seems so stupid,
it makes me want to give up.
[EMAIL PROTECTED] But why should I give up,
when it all seems so stupid?
MAKE INSTALL NOT WAR And please avoid Necrosoft Widows
