pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelecting count of details along with details columns
6+ messages / 3 participants
[nested] [flat]
* Selecting count of details along with details columns
@ 2005-09-28 19:19 Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 08:30 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Axel Rau @ 2005-09-28 19:19 UTC (permalink / raw)
To: pgsql-sql
Dear pgsql-admin members:
Having 2 tables:
CREATE TABLE T2 (
id serial PRIMARY KEY,
T2_name text
);
CREATE TABLE T1 (
id serial PRIMARY KEY,
T1_name text,
fk_t2 int4 REFERENCES t2
);
And some rows:
INSERT INTO T2 (T2_name) VALUES('T2-N1');
INSERT INTO T2 (T2_name) VALUES('T2-N2');
INSERT INTO T2 (T2_name) VALUES('T2-N3');
INSERT INTO T2 (T2_name) VALUES('T2-N4');
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-AAA', 1);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-BBB', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-CCC', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-DDD', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-EEE', 3);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-FFF', 3);
It is possible to show how many details exist and to limit result with
HAVING:
SELECT T2.T2_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | xx
---------+----
T2-N2 | 3
T2-N3 | 2
(2 rows)
Adding column t1_name to the result set breaks COUNT(T1.id):
SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name, T1.T1_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | t1_name | xx
---------+---------+----
(0 rows)
How can I do this with pg ?
Looking for a workaround, I learned that aggregate functions are not
allowed in WHERE clauses.
Question: Is this conform with the standard?
Sorry, if this has been discussed earlier.
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting count of details along with details columns
2005-09-28 19:19 Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
@ 2005-09-29 08:30 ` Richard Huxton <dev@archonet.com>
2005-09-29 09:19 ` Re: Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-09-29 08:30 UTC (permalink / raw)
To: Axel Rau <Axel.Rau@Chaos1.DE>; +Cc: pgsql-sql
Axel Rau wrote:
>
> SELECT T2.T2_name, COUNT(T1.id) AS xx
> FROM T2, T1
> WHERE T2.id = T1.fk_t2
> GROUP BY T2.T2_name
> HAVING COUNT(T1.id) > 1
> ORDER BY xx DESC;
>
> t2_name | xx
> ---------+----
> T2-N2 | 3
> T2-N3 | 2
> (2 rows)
>
> Adding column t1_name to the result set breaks COUNT(T1.id):
>
> SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
> FROM T2, T1
> WHERE T2.id = T1.fk_t2
> GROUP BY T2.T2_name, T1.T1_name
> HAVING COUNT(T1.id) > 1
> ORDER BY xx DESC;
>
> t2_name | t1_name | xx
> ---------+---------+----
> (0 rows)
>
> How can I do this with pg ?
Do what? You don't say what results you are expecting.
Do you want:
1. ALL values of T1_name (in which case what count do you want)?
2. The FIRST value of T1_name (in which case what do you mean by first)?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting count of details along with details columns
2005-09-28 19:19 Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 08:30 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
@ 2005-09-29 09:19 ` Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 10:03 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Axel Rau @ 2005-09-29 09:19 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Am 29.09.2005 um 10:30 schrieb Richard Huxton:
> Axel Rau wrote:
>> SELECT T2.T2_name, COUNT(T1.id) AS xx
>> FROM T2, T1
>> WHERE T2.id = T1.fk_t2
>> GROUP BY T2.T2_name
>> HAVING COUNT(T1.id) > 1
>> ORDER BY xx DESC;
>> t2_name | xx
>> ---------+----
>> T2-N2 | 3
>> T2-N3 | 2
>> (2 rows)
>> Adding column t1_name to the result set breaks COUNT(T1.id):
>> SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
>> FROM T2, T1
>> WHERE T2.id = T1.fk_t2
>> GROUP BY T2.T2_name, T1.T1_name
>> HAVING COUNT(T1.id) > 1
>> ORDER BY xx DESC;
>> t2_name | t1_name | xx
>> ---------+---------+----
>> (0 rows)
>> How can I do this with pg ?
>
> Do what? You don't say what results you are expecting.
>
> Do you want:
> 1. ALL values of T1_name (in which case what count do you want)?
> 2. The FIRST value of T1_name (in which case what do you mean by
> first)?
#1.:
t2_name | t1_name | count
---------+---------+-------
T2-N2 | T1-CCC | 3
T2-N3 | T1-FFF | 2
T2-N2 | T1-BBB | 3
T2-N2 | T1-DDD | 3
T2-N3 | T1-EEE | 2
(5 rows)
Sorry, for not making this clear.
Thank you for taking the time,
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
Attachments:
PGP.sig (application/pgp-signature, 477B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting count of details along with details columns
2005-09-28 19:19 Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 08:30 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
2005-09-29 09:19 ` Re: Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
@ 2005-09-29 10:03 ` Richard Huxton <dev@archonet.com>
2005-09-29 19:08 ` Re: Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-09-29 10:03 UTC (permalink / raw)
To: Axel Rau <Axel.Rau@Chaos1.DE>; +Cc: pgsql-sql
Axel Rau wrote:
>
> Am 29.09.2005 um 10:30 schrieb Richard Huxton:
>
>> Axel Rau wrote:
>>
>>> SELECT T2.T2_name, COUNT(T1.id) AS xx
>>> FROM T2, T1
>>> WHERE T2.id = T1.fk_t2
>>> GROUP BY T2.T2_name
>>> HAVING COUNT(T1.id) > 1
>>> ORDER BY xx DESC;
>>> t2_name | xx
>>> ---------+----
>>> T2-N2 | 3
>>> T2-N3 | 2
>>> (2 rows)
>>> Adding column t1_name to the result set breaks COUNT(T1.id):
>>> SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
>>> FROM T2, T1
>>> WHERE T2.id = T1.fk_t2
>>> GROUP BY T2.T2_name, T1.T1_name
>>> HAVING COUNT(T1.id) > 1
>>> ORDER BY xx DESC;
>>> t2_name | t1_name | xx
>>> ---------+---------+----
>>> (0 rows)
>>> How can I do this with pg ?
>>
>>
>> Do what? You don't say what results you are expecting.
>>
>> Do you want:
>> 1. ALL values of T1_name (in which case what count do you want)?
>> 2. The FIRST value of T1_name (in which case what do you mean by first)?
>
>
> #1.:
>
> t2_name | t1_name | count
> ---------+---------+-------
> T2-N2 | T1-CCC | 3
> T2-N3 | T1-FFF | 2
> T2-N2 | T1-BBB | 3
> T2-N2 | T1-DDD | 3
> T2-N3 | T1-EEE | 2
Ah - this is two questions:
1. What are the unique (t2_name,t1_name) pairings?
2. How many different (t1.id) values are there for each t2.
So - something like:
SELECT names.T2_name, names.T1_name, counts.num_t2
FROM
(
SELECT DISTINCT T2.T2_name, T1.T1_name
FROM T2,T1
WHERE T2.id = T1.fk_t2
) AS names,
(
SELECT T2.T2_name, COUNT(T1.id) AS num_t2
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name
HAVING COUNT(T1.id) > 1
) AS counts
WHERE
names.T2_name = counts.T2_name
;
You could write the "names" sub-query with a GROUP BY if you wanted of
course.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting count of details along with details columns
2005-09-28 19:19 Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 08:30 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
2005-09-29 09:19 ` Re: Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 10:03 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
@ 2005-09-29 19:08 ` Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 19:29 ` Re: Selecting count of details along with details columns Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 6+ messages in thread
From: Axel Rau @ 2005-09-29 19:08 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Am 29.09.2005 um 12:03 schrieb Richard Huxton:
> Axel Rau wrote:
>> Am 29.09.2005 um 10:30 schrieb Richard Huxton:
>>> Axel Rau wrote:
>>>
>>>>
...
> Ah - this is two questions:
> 1. What are the unique (t2_name,t1_name) pairings?
> 2. How many different (t1.id) values are there for each t2.
>
> So - something like:
>
> SELECT names.T2_name, names.T1_name, counts.num_t2
> FROM
> (
> SELECT DISTINCT T2.T2_name, T1.T1_name
> FROM T2,T1
> WHERE T2.id = T1.fk_t2
> ) AS names,
> (
> SELECT T2.T2_name, COUNT(T1.id) AS num_t2
> FROM T2, T1
> WHERE T2.id = T1.fk_t2
> GROUP BY T2.T2_name
> HAVING COUNT(T1.id) > 1
> ) AS counts
> WHERE
> names.T2_name = counts.T2_name
> ;
>
> You could write the "names" sub-query with a GROUP BY if you wanted of
> course.
Exactly, that query works as I expected. Thank you.
Can you answer this question as well:
> Looking for a workaround, I learned that aggregate functions are not
> allowed in WHERE clauses.
> Question: Is this conform with the standard?
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)
iQEVAwUBQzw7n8Fz9+6bacTRAQIqnAf9EW7TS7K+cCf95fosagOcNhgQFuUvlyUr
yJpkXrv83+oKJ6kw6OcJxaEAkuiyRIiGQAlsVfc86itgKUQLfq6qpXEjeMD459kb
wIO01LV37akn9y3420h4Pmi1SDaZ63oUWJn48DhlUuuh5B7LHNyiOSMUKLU8ptLd
ZQ875uPo235bdqb15ibmZtwAuMGdsf3PPySBYMzvHzk7uZ+68b50QTmTPSU7VuPd
XtbZWdTK8q6+R3mhgz6k7DFaqTlTqzMimQevmwb1ADZZGVOOC0i77M1axYsCHarB
i2RT1CAcnNCX8MYc2nt8HS4j5KXpq7POFk3vdyAmVMwZ8WHNWJP2/w==
=CJSg
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting count of details along with details columns
2005-09-28 19:19 Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 08:30 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
2005-09-29 09:19 ` Re: Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 10:03 ` Re: Selecting count of details along with details columns Richard Huxton <dev@archonet.com>
2005-09-29 19:08 ` Re: Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
@ 2005-09-29 19:29 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2005-09-29 19:29 UTC (permalink / raw)
To: Axel Rau <Axel.Rau@chaos1.de>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
On Thu, 2005-09-29 at 14:08, Axel Rau wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> Exactly, that query works as I expected. Thank you.
> Can you answer this question as well:
> > Looking for a workaround, I learned that aggregate functions are not
> > allowed in WHERE clauses.
> > Question: Is this conform with the standard?
The problem is that where clauses "fire" before group by does, so the
aggregate doesn't exist yet when the where clause would be doing it's
selection.
Which is why the spec, and postgresql, have the "having" clause...
See this part of the docs:
http://www.postgresql.org/docs/8.0/interactive/sql-select.html
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-09-29 19:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-28 19:19 Selecting count of details along with details columns Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 08:30 ` Richard Huxton <dev@archonet.com>
2005-09-29 09:19 ` Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 10:03 ` Richard Huxton <dev@archonet.com>
2005-09-29 19:08 ` Axel Rau <Axel.Rau@Chaos1.DE>
2005-09-29 19:29 ` Scott Marlowe <smarlowe@g2switchworks.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox