Problem with aggregates and group by
От | Memphisto |
---|---|
Тема | Problem with aggregates and group by |
Дата | |
Msg-id | Pine.LNX.3.96.981030162539.20039J-100000@linux.intranet.netvisor.hu обсуждение исходный текст |
Ответы |
Re: [GENERAL] Problem with aggregates and group by
(Ulf Mehlig <umehlig@uni-bremen.de>)
Transaction aborted? (Mike Meyer <mwm@phone.net>) |
Список | 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 по дате отправления: