Thread: Problem with aggregates and group by
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
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
~~~~~~~~~~~~~~~~~~~~
You are right. Thanks.
Perhaps originally I used a query that only used the annex_log table, but
this app I'm writing is generating the queries and there's also a query
that does the same job but FOR groups and there I shall use all the three
tables. I store the parts of the query string in C constant strings and
some parts of these two queries were in the same C strings. When I
elaborated the 'groups' query I modified these strings that's why I got
that very very strange query for 'users'.
Anyway thanks a lot.
--------------------------------------------------------------------------------
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
I'm seeing these messages in the process of adding entries to a
database.
NOTICE: (transaction aborted): queries ignored until END
I'm not positive what they mean. Is there someone who is who could
tell me, and tell me how to catch then and deal with them?
Thanx,
<mike
On Fri, 30 Oct 1998, Ulf Mehlig wrote: > 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 ;-) The only SQL book I've ever seen recommened (other than the vendor manuals) is "The Practical SQL Handbook" by Bowman, Emerson and Darnovsky. The authors are (were?) Sybase tech writers, and the book shows a Sybase bias. But it treys to point out where that is happening. The Amazon search turns up the current one, as well as the out-of-print version I have. <URL: http://www.amazon.com/exec/obidos/external-search/002-3198110-4570053?mode=books&keyword=The+Practical+SQL+Handbook&tag=meyerconsulting > <mike
Mike Meyer wrote: > > I'm seeing these messages in the process of adding entries to a > database. > > NOTICE: (transaction aborted): queries ignored until END > > I'm not positive what they mean. Is there someone who is who could > tell me, and tell me how to catch then and deal with them? > That's mean that you had an error during transaction. The only command that backend accepts at this point is the END. Jose'