Thread: count different values in column
Hello, today I have a new question :) I've a resulttable is generated by a couple of subselects and joins which contains only one column. Now I want to count the different values in the column. Resulttable : a ------ 10 12 12 12 14 14 15 I need the value which is repeated most in the resulttable (in this example 12 ) ! Any suggestions ? Thx berger
On Fri, 19 Apr 2002, Albrecht Berger wrote: > Now I want to count the different values in the column. > > Resulttable : > a > ------ > 10 > 12 > 12 > 12 > 14 > 14 > 15 > > I need the value which is repeated most in the resulttable (in this example > 12 ) ! SELECT a, count(a) FROM resulttable GROUP BY a ORDER BY 2 DESC -- Tod McQuillin
Hi Berger, Try, select a from Resulttable group by a having count(a) = (select max(cnt) from (select count(a) as cnt from Resulttable group by a) as tmp); Regards, Gautham. ----- Original Message ----- From: "Albrecht Berger" <berger1517@gmx.ch> To: "pgsql" <pgsql-sql@postgresql.org> Sent: Friday, April 19, 2002 3:00 PM Subject: [SQL] count different values in column > Hello, > today I have a new question :) > > I've a resulttable is generated by a couple of subselects and joins which > contains only one column. > > Now I want to count the different values in the column. > > Resulttable : > a > ------ > 10 > 12 > 12 > 12 > 14 > 14 > 15 > > I need the value which is repeated most in the resulttable (in this example > 12 ) ! > > > Any suggestions ? > > > Thx > berger > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
select max(count) from ( select a, count(a) from Resulttable group by a ) as foo; max ----- 3 (1 row) should do it. Regards, Christoph > Hello, > today I have a new question :) > > I've a resulttable is generated by a couple of subselects and joins which > contains only one column. > > Now I want to count the different values in the column. > > Resulttable : > a > ------ > 10 > 12 > 12 > 12 > 14 > 14 > 15 > > I need the value which is repeated most in the resulttable (in this example > 12 ) ! > > > Any suggestions ? > > > Thx > berger >
On Fri, Apr 19, 2002 at 04:40:35AM -0500, Tod McQuillin wrote: > On Fri, 19 Apr 2002, Albrecht Berger wrote: > > > Now I want to count the different values in the column. > > > > Resulttable : > > a > > ------ > > 10 > > 12 > > 12 > > 12 > > 14 > > 14 > > 15 > > > > I need the value which is repeated most in the resulttable (in this example > > 12 ) ! > > SELECT a, count(a) FROM resulttable GROUP BY a ORDER BY 2 DESC Note that count on NULL values will always give you 0: fduch=> INSERT INTO resulttable VALUES (NULL); INSERT 1702218 1 (repeat some times) fduch=> SELECT a, count(a) FROM resulttable GROUP BY a ORDER BY 2 DESC;a | count ----+-------12 | 314 | 210 | 115 | 1 | 0 (5 rows) -- Fduch M. Pravking