Thread: using 'count' to show number of dupes
I'm writing some perl to sendmail maillog reporting. Since the logs are too big to some of the things I want to do, I'm just stuffing it to a pgsql db. I'd like to offload as much of the work onto the db as much as possible. How can I use count to show rows with the same value in the column? col1 col2 col3 foo1 bar1 baz1 foo2 bar1 baz2 foo3 bar2 baz3 foo4 bar3 baz4 foo5 bar1 baz5 foo6 bar2 baz6 So, what would get pg to show me that col2 has 3 'bar1' values, 2 'bar2' values and 1 'bar1' value. -- Andy Harrison
On Mon, 12 Jul 2004, Andy Harrison wrote: > I'm writing some perl to sendmail maillog reporting. Since the logs > are too big to some of the things I want to do, I'm just stuffing it > to a pgsql db. I'd like to offload as much of the work onto the db as > much as possible. > > How can I use count to show rows with the same value in the column? > > col1 col2 col3 > foo1 bar1 baz1 > foo2 bar1 baz2 > foo3 bar2 baz3 > foo4 bar3 baz4 > foo5 bar1 baz5 > foo6 bar2 baz6 > > > So, what would get pg to show me that col2 has 3 'bar1' values, 2 > 'bar2' values and 1 'bar1' value. Something like: select col2, count(*) from table group by col2;
Write a queries like this... SELECT col1, count(col1) as count_col1 FROM <table> GROUP BY col1 -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andy Harrison Sent: Monday, July 12, 2004 1:29 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] using 'count' to show number of dupes I'm writing some perl to sendmail maillog reporting. Since the logs are too big to some of the things I want to do, I'm just stuffing it to a pgsql db. I'd like to offload as much of the work onto the db as much as possible. How can I use count to show rows with the same value in the column? col1 col2 col3 foo1 bar1 baz1 foo2 bar1 baz2 foo3 bar2 baz3 foo4 bar3 baz4 foo5 bar1 baz5 foo6 bar2 baz6 So, what would get pg to show me that col2 has 3 'bar1' values, 2 'bar2' values and 1 'bar1' value. -- Andy Harrison ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
On Mon, 12 Jul 2004 10:39:00 -0700 (PDT), Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > Something like: > select col2, count(*) from table group by col2; > Great, thanx. That got me on the right road. Fast too. Several mere seconds for 1.25M rows. -- Andy Harrison