Count distinct dates within timestamp by Month SQL

user_11471104 0
Viewed 912

I would like to count the number of distinct dates within each month.

I have a data set that looks like this:

TIMESTAMP 
------------------
2017-10-25 14:39:51 
2017-10-25 15:00:51
2017-11-10 02:39:42
2018-09-24 14:39:55
2018-09-25 13:25:01
2019-02-12 12:23:44
...

So my expected output would be:

year_month | count
2017-10    2
2018-09    2
2019-02    1

I have tried the following code so far, but it is returning incorrect results:

WITH F AS(
    SELECT concat(YEAR(TIMESTAMP), MONTH(TIMESTAMP)) AS year_month
        FROM tbl
        WHERE TYPE = 'Site'
            AND TO_SITE = 'location'
)

SELECT count(year_month), year_month
    FROM F
    GROUP BY year_month

I do not need to worry about the time of day. I just want to count the distinct days in each month. Thank you in advance for your help.

sql-server sql
3 Answers

from your code I assume you are using a SQL Server then you can do something like this

with cte as
(
  select
    left(convert(varchar, myCol,112),6) as yyyy_mm,
    convert(date, myCol) as date
  from myTable
)

select 
  yyyy_mm,
  count(distinct date) as count
from cte
group by
  yyyy_mm

output:

| yyyy_mm | count |
*-----------------*
| 201710  | 1     |
| 201711  | 1     |
| 201809  | 2     |
| 201902  | 1     |
user_86913190

In SQL Server, I would recommend one of these approaches:

select year(timestamp), month(timestamp), count(distinct convert(date, timestamp)
from t
group by year(timestamp), month(timestamp);

Or:

select format(timestamp, 'yyyy-MM'), count(distinct convert(date, timestamp))
from t
group by format(timestamp, 'yyyy-MM');

I see no need for subqueries or CTEs.

user_11440350

Using date_trunc() in PostgreSQL it can simply be:

SELECT date_trunc('month', timestamp)
     , count(DISTINCT date_trunc('day', timestamp))
FROM   tbl
GROUP  BY 1;

Various performance optimizations possible, depending on details of the setup.

user_9398600
Related