Re: [GENERAL] Problem with aggregates and group by
От | Ulf Mehlig |
---|---|
Тема | Re: [GENERAL] Problem with aggregates and group by |
Дата | |
Msg-id | 199810301647.RAA07593@uni-bremen.de обсуждение исходный текст |
Ответ на | Problem with aggregates and group by (Memphisto <szoli@netvisor.hu>) |
Ответы |
Re: [GENERAL] Problem with aggregates and group by
(Memphisto <szoli@netvisor.hu>)
Re: [GENERAL] Problem with aggregates and group by (Mike Meyer <mwm@phone.net>) |
Список | pgsql-general |
Sebesty�n Zolt�n 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.mehlig@uni-bremen.de> %%%%!%%% Projekt "MADAM" <umehlig@uni-bremen.de> %%%% %!% %%%% ---------------------------------------------------- ---| %%% MADAM: MAngrove | Center for Tropical Marine ||--%!% Dynamics | Biology || And | Fahrenheitstrasse 1 _ /||\_/\_ Management | / / \ \ ~~~~~~~~~~~~~~~~~ | 28359 Bremen/Germany ~~~~~~~~~~~~~~~~~~~~
В списке pgsql-general по дате отправления: