Counting unique values

Database

Counting unique values

If I have a table for example:




ID Name Job Count
123456 Jim dinner 1
123456 Jim lunch 1
123456 Jim break 2
123456 Jim break 0
234567 Paul dinner 3
234567 Paul dinner 0
234567 Paul dinner 0

How would I create query for the unique count?

1 REPLY
N/A

Re: Counting unique values

I  can't imagine why you need it, but this should be close:

SELECT 
id, name, job,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY id,job,name ORDER BY id) = 1
THEN COUNT(*) OVER (PARTITION BY id,job,name)
ELSE 0 END
FROM tab

Dieter