Postgres Pro
Downloads
Home   >   mailing lists

Thread: Baffled by "Group By" - Please help!

Baffled by "Group By" - Please help!

From
"Matt Friedman"
Date:
For some reason, probably because I'm not understanding very well, I'm
getting some duplicated rows for this query (below).

My understanding is that "group by" brings all the rows together where the
indicated columns are equal and then applies the aggregate to the column
indicated; in this case: "sum".

What am I missing? I'm not seeing why this query doesn't group everything
and then return unique rows.

Please let me know if you need more info and I will send it.

SELECT
 index_uri.uri,
 index_uri.description,
 index_uri.title,
 index_type.type,
 index_type.icon,
 SUM(index.word_count)
FROM
 index, index_word, index_uri, index_type
WHERE
 (
   index_word.word ~'eat.*'             -- could be more or fewer
comparisons here.
   OR index_word.word ~'disord.*'
 )
AND
 index_word.word_id=index.word_id
AND
 index_uri.uri_id = index.uri_id
AND
 index_type.type_id = index_uri.type_id
GROUP BY
 index_uri.uri,
 index_uri.description,
 index_uri.title,
 index_type.type,
 index.word_count,
 index_type.icon
ORDER BY
 sum
DESC

Matt Friedman

Re: Baffled by "Group By" - Please help!

From
Tom Lane
Date:
"Matt Friedman" <matt@daart.ca> writes:
> What am I missing? I'm not seeing why this query doesn't group everything
> and then return unique rows.

Since you didn't show an example of the input and output, it's hard to
tell what your gripe is ... but I'll venture that you didn't want to
group by index.word_count.  It doesn't make a lot of sense to group by
something you are using as an aggregate input.

            regards, tom lane

Re: Baffled by "Group By" Resolved and Thanks

From
"Matt Friedman"
Date:
Yeah, it was right under my nose. Figured it out just before I got your
email.

Thank you.

Matt Friedman


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Matt Friedman" <matt@sprynewmedia.com>
Cc: "PgSql General List" <pgsql-general@postgresql.org>
Sent: Saturday, March 24, 2001 1:34 PM
Subject: Re: Baffled by "Group By" - Please help!


> "Matt Friedman" <matt@daart.ca> writes:
> > What am I missing? I'm not seeing why this query doesn't group
everything
> > and then return unique rows.
>
> Since you didn't show an example of the input and output, it's hard to
> tell what your gripe is ... but I'll venture that you didn't want to
> group by index.word_count.  It doesn't make a lot of sense to group by
> something you are using as an aggregate input.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>