Re: [HACKERS] VIEWS, DISTINCT and COUNT

Brian Hirt wrote: >Okay, > >I'm used to a feature that allows combining count and distinct in Sybase.  >I thought it
wasstandard SQL and expected to see it in Postgres.  Whatever >the case might be, postgres does not seem to support
this. I keep running >into queries that I cannot write.  Here's the skinny: > >"select count(distinct id) from table"
isnot supported. 
 

I'm not convinced I understand what your query would do, but it sounds as
if you need to use GROUP BY.  For example:

lfix=> select custid, count(custid) from invoice group by custid;
custid  |count
--------+-----
ACECS   |    1
ADG     |    8
FALKIRK |    1
JEFSYS  |   25
SOLPORT |   15
(5 rows)

lfix=> select count(*) from invoice;
count
-----  50
(1 row)

Is that what you want to achieve?

--      Vote against SPAM: http://www.politik-digital.de/spam/                ========================================
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "Lo, children are an heritage of the LORD; and
the      fruit of the womb is his reward."        Psalms 127:3