Postgres Pro
Downloads
Home   >   mailing lists

Thread: Selecting count of details along with details columns

Selecting count of details along with details columns

From
Axel Rau
Date:
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

Re: Selecting count of details along with details columns

From
Richard Huxton
Date:
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

Re: Selecting count of details along with details columns

From
Axel Rau
Date:

			
		

		
	

	
							

	

	
	

		

			
Re: Selecting count of details along with details columns

									
		    

		        
From
 
		    	
Richard Huxton

		    

		    

		        
Date:

		        




			

		


		

			
				
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




			
		

		
	

	
							

	

	
	

		

			
Re: Selecting count of details along with details columns

									
		    

		        
From
 
		    	
Axel Rau

		    

		    

		        
Date:

		        




			

		


		

			
				
-----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-----





			
		

		
	

	
							

	

	
	

		

			
Re: Selecting count of details along with details columns

									
		    

		        
From
 
		    	
Scott Marlowe

		    

		    

		        
Date:

		        




			

		


		

			
				
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




			
		

		
	

	
							

	

	
	

		

			
unsubscribe

									
		    

		        
From
 
		    	
"Ricky Sutanto"

		    

		    

		        
Date:

		        




			

		


		

			
				
<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>