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, Andy Harrison wrote:

Something like:
select col2, count(*) from table group by col2;

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Write a queries like this...

SELECT col1, count(col1) as count_col1
FROM <table>
GROUP BY col1


-----Original Message-----
From: pgsql-novice-owner (AT) postgresql (DOT) org
[mailtogsql-novice-owner (AT) postgresql (DOT) org] On Behalf Of Andy Harrison
Sent: Monday, July 12, 2004 1:29 PM
To: pgsql-novice (AT) postgresql (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Mon, 12 Jul 2004 10:39:00 -0700 (PDT), Stephan Szabo
<sszabo (AT) megazone (DOT) bigpanda.com> wrote:
Great, thanx. That got me on the right road. Fast too. Several mere
seconds for 1.25M rows.

--
Andy Harrison

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org