Problem with aggregates and group by - Mailing list pgsql-general

From Memphisto
Subject Problem with aggregates and group by
Date
Msg-id Pine.LNX.3.96.981030162539.20039J-100000@linux.intranet.netvisor.hu
Whole thread Raw
Responses Re: [GENERAL] Problem with aggregates and group by
Transaction aborted?
List pgsql-general
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.
szoli@netvisor.hu            But why should I give up,
                                        when it all seems so stupid?

MAKE INSTALL NOT WAR            And please avoid Necrosoft Widows



pgsql-general by date:

Previous
From: Lorenzo Huerta
Date:
Subject: Re: [GENERAL] question on views...
Next
From: Lee Roth
Date:
Subject: backslash D in psql fails