Обсуждение: Selecting count of details along with details columns
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
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
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
-----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-----
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
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Please unsubscribe me</span></font></div>