How to count only once for a specified record?

Question

How to count only once for a specified row even if the row exists more than once?

For instance I have a SQL query

SELECT COUNT(Date) 
FROM mytable 
WHERE name like '%John%'

But it counts all John's number. What I need is, if I find a name John, I want to count only one time, no matter John's name exists more than once.

To illustrate

Name       Date
John       06-09-2012 1am
Robert     06-09-2012 2am
John       06-09-2012 3am 
John       06-09-2012 4am
Robert     06-09-2012 5am

Results should be 1 for John.

you mean to ignore the time component ? http://stackoverflow.com/a/113055/570150 — V4Vendetta, Aug 09 '12 at 06:20
My intention is to count appearance of each John in terms of day, but i did not mention that. If you consider with that way, how to write sql query? — John Smith, Aug 09 '12 at 06:23

Himanshu · Accepted Answer · 2012-08-09T06:54:46.207

1

You need to COUNT Distinct Date and also need to use GROUP BY clause like this:

SELECT Name,COUNT(DISTINCT CONVERT(varchar,date,103)) AS NameCount
FROM mytable WHERE name LIKE '%John%'
GROUP BY name

SEE THIS FIDDLE for name LIKE '%John%'.

Also, for each user try this:

SELECT Name,COUNT(DISTINCT CONVERT(varchar,date,103)) AS NameCount
FROM mytable GROUP BY name

edited Aug 09 '12 at 06:54

answered Aug 09 '12 at 06:26

Himanshu

@JohnSmith See the answer again which is more improved. – Himanshu Aug 09 '12 at 06:55

Kamal · Answer 2 · 2012-08-09T06:53:17.957

-1

Update query as per clear requirements

For oracle

SELECT count(distinct (extract day from date)) FROM mytable WHERE name like '%John%' and Date is not null;

edited Aug 09 '12 at 06:53

answered Aug 09 '12 at 06:20

Kamal

Thanks, What about count each name for each day. The results should be 1 for 06-09-2012 with name=john, and 1 for 07-09-2012 with name=john. So, for each day john should be counted. How can I do that ? – John Smith Aug 09 '12 at 06:35
@JohnSmith [See this answer](http://stackoverflow.com/questions/11877717/how-to-count-only-once-for-a-specified-record/11877846#11877846) again. This is exactly what you want. – Himanshu Aug 09 '12 at 06:37
Sorry I did not you question first time, answer by @hims056 will solve your problem – Kamal Aug 09 '12 at 06:52

2 Answers2