Обсуждение: finding duplicate numbers in a select distinct statement

Hello all,

I am trying to do the following query but I would like to know how
many rows were deduplicated in the process:

psql -d foo -c 'SELECT DISTINCT ON (error) error,ev_text FROM
clients_event_log' -o fullfoo


For example, I would want the output to be the following where count
is the number of error rows that were deduplicated.

count    |   error    |   ev_text
55         |  525152 |   Some text
72         |  125124 |   Some other text

I imagine it's using the count function somewhere, just not sure how
to embed that.

select count(*) AS count, error, ev_text FROM clients_event_log GROUP BY error, ev_text;

you can add 'HAVING count(*) > X'; , if you want to see only those with count above X, etc.


--
GJ

2010/3/3 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
> select count(*) AS count, error, ev_text FROM clients_event_log GROUP BY
> error, ev_text;
>
> you can add 'HAVING count(*) > X'; , if you want to see only those with
> count above X, etc.
>
>
> --
> GJ
>

I was just about to reply to the group.  Would this work too?
psql -d foo -c 'SELECT COUNT(DISTINCT(error)) AS count,error,ev_text
FROM dsclient_logs WHERE ev_id > 23572151 GROUP BY error,ev_text'

just try if it does what you want it to do ;)

2010/3/3 Terry <td3201@gmail.com>:
> 2010/3/3 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
>> select count(*) AS count, error, ev_text FROM clients_event_log GROUP BY
>> error, ev_text;
>>
>> you can add 'HAVING count(*) > X'; , if you want to see only those with
>> count above X, etc.
>>
>>
>> --
>> GJ
>>
>
> I was just about to reply to the group.  Would this work too?
> psql -d foo -c 'SELECT COUNT(DISTINCT(error)) AS count,error,ev_text
> FROM dsclient_logs WHERE ev_id > 23572151 GROUP BY error,ev_text'
>

My statement was broke.  GJ's was spot on.  Thanks!