Thread: count & distinct
Hi people. I would like to make a query that tells me how many distinct values there are in a column. Standard count doesn't seems to support a count distinct option. select distint count(*) of course doens't work (distinti clause is applied after the result are calculated). I've tried to define a view, but it didn't worked ( create view distValues as select distinct ... but views doesn't support distinct clause)... Well, maybe I should create a new aggregate function, but before spending time on PGSQL guide I would like to know if someone can give me a fast tip... ;-) Thanks in advance Andrea PS: well, of course I can open a cursor on a "select distinct column from table" and then loop over the cursor couting how many values there are, but it seem a bit ugly...
> Hi people. I would like to make a query that > tells me how many distinct values there are > in a column. Standard count doesn't seems > to support a count distinct option. > select distint count(*) of course doens't > work (distinti clause is applied after the > result are calculated). I've tried to define > a view, but it didn't worked ( > create view distValues as select distinct ... > but views doesn't support distinct clause)... SELECT COUNT(DISTINCT field_name) FROM table_name; Works for me in v7. Regards, Andrew Snow als@fl.net.au
Andrea Aime wrote: > > Hi people. I would like to make a query that > tells me how many distinct values there are > in a column. Standard count doesn't seems > to support a count distinct option. > select distint count(*) of course doens't > work (distinti clause is applied after the > result are calculated). I've tried to define > a view, but it didn't worked ( > create view distValues as select distinct ... > but views doesn't support distinct clause)... > > Well, maybe I should create a new aggregate > function, but before spending time on PGSQL > guide I would like to know if someone can > give me a fast tip... ;-) > Thanks in advance > Andrea > > PS: well, of course I can open a cursor on > a "select distinct column from table" and then > loop over the cursor couting how many values > there are, but it seem a bit ugly... SELECT field, count(*) FROM table GROUP BY field With best regards, Max Vaschenko, Nizhny Novgorod Information Networks.