Forum OpenACS Q&A: SQL: Distinct Counting Help

Posted by Bob OConnor on

Ok this is a *simple* one that has me baffled at the moment. I have a table that tracks page views of logged on users. Every time they go to the page, I store their user_id. Now I only want the number of rows which would be a count of unique persons viewing the page...

select distinct user_id, count(*) from page_view 
group by user_id;

This gives me

     12647 |     1
     12666 |     4
     12730 |     1
     12734 |     2
     12738 |     1
(1088 rows)

I only want one row returned that has the 1088 number. I plan to use something like:

set n_unique [database_to_tcl_string $db "select
***SQLhere*** "]

I re-ran the above query without the "distinct" and get the same answer so
THANK you.


Posted by Don Baccus on
select count(*) from (select distinct user_id from page_view) foo;

PG at least requires the subselect alias (foo) ...

Posted by Don Baccus on
Or, actually, PG does support SQL92's "count(distinct ..)" so

select count(distinct user_id) from page_view;