CODEWITHSUNDEEP
sqlsql-server
Brandon Brown
Brandon Brown

Reputation: 29

SQL Use ID to count dates but only can count each once

TABLE 1

  ID   DATE
  1 . 1/1/2018
  2 . 1/2/2018
  2 . 1/2/2018
  3 . 1/3/2018
  4 . 1/2/2018

So I need to get a count of each date but there are cases where the same ID has the same date. I need to only count that one once.

For example my expected output is

  1/1/2018 .  1
  1/2/2018 .  2
  1/3/2018 .  1

But the output i'm getting is

  1/1/2018 .  1
  1/2/2018 .  3
  1/3/2018 .  1

Upvotes: 1

Views: 1313

Answers (3)

Karlomanio
Karlomanio

Reputation: 371

You could use a subquery where you concatenate the key and the date using distinct.

SELECT DATE, COUNT(DATE) FROM table1 
where DATE + ID = 
(select DATE + ID from table1) 
group by DATE

Upvotes: 0

forpas
forpas

Reputation: 164089

You need to GROUP by ID and DATE:

SELECT ID, DATE, COUNT(DATE) AS COUNTER
FROM table1
GROUP BY ID, DATE

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need DISTINCT inside COUNT() to considered same id as once :

SELECT DATE, COUNT(DISTINCT ID)
FROM table1 t
GROUP BY DATE;

Upvotes: 3

Related Questions