CODEWITHSUNDEEP
sqlpostgresql
ian0411
ian0411

Reputation: 4265

Count within two columns with a condition

I have data as follows:

Name|  Place|   TS
   A|     AA|   12/25/2017 0:00
   A|     AA|   12/31/2017 0:00
   A|     AA|   1/10/2018 0:00
   A|     AA|   1/15/2018 0:00
   A|     AA|   1/31/2018 0:00
   A|     AA|   2/3/2018 0:00
   A|     AA|   2/5/2018 0:00
   A|     AA|   2/7/2018 0:00
   A|     AA|   2/8/2018 0:00
   A|     BB|   1/1/2018 0:00
   A|     BB|   1/22/2018 0:00
   A|     BB|   2/5/2018 0:00

What I need to accomplish here is to count how many times one person visited one place within certain days. For example, person A visited place AA 4 times in 7 days and place BB 1 time (count from today). My expected outcome should look like below:

Name    Place   Last_Week   Last_Month
A       AA      4           7
A       BB      1           2

Below is what I currently have but I have a hard time to aggregate the counts with the condition. Please help and thank you so much.

SELECT
  Name, Place, COUNT(*)
FROM
  SampleTable
GROUP BY
  Name, Place, TS
HAVING
  TS >= now()::date - 7
ORDER BY
  Name, Place;

Upvotes: 1

Views: 90

Answers (2)

Denys Séguret
Denys Séguret

Reputation: 382150

The simplest solution is to use FILTER:

select
name,
place,
count(*) filter (where TS >= now()::date - 7) last_week,
count(*) filter (where TS >= now()::date - 30) last_month
from sampleTable group by name, place order by name, place

See documentation

Upvotes: 2

Kobi
Kobi

Reputation: 2524

You can group your data by name and place, then count how many time the dates fit in your constraint

 select
    name,
    place,
    sum(case when lw.TS >= now()::date - 7 then 1 else 0 end) last_week,
    sum(case when lw.TS >= now()::date - 30 then 1 else 0 end) last_month
    from sampleTable st
    GROUP BY
      Name, Place
    order by name, place

Upvotes: 4

Related Questions