How can I get a user's activity count for today and this month in a single SELECT query

user_39371 0
Viewed 65

In my table I have:

Activity : Date
---------------
doSomething1 : June 1, 2020
doSomething2 : June 14, 2020

I want to be able to make a query so that I can get the following result (assuming today is June 1, 2020):

Today : ThisMonth
1 : 2

I looked at group by but I wasn't sure how to do that without a lot of additional code and I think there's very likely a much simpler solution that I'm missing. Something that will just return a single row with two results. Is this possible and if so how?

select group-by postgresql sql
3 Answers

Is this what you want?

select array_agg(activity) filter (where date = current_date) as today,
       array_agg(activity) filter (where date <> current_date) as rest_of_month
from t
where date_trunc('month', date) = current_date;

This uses arrays so it can handle more than one activity in either category.

user_11440350

you can write subqueries to get data in single row,

Select today , month 
from
(
( query to get today's count ) as today,
( query to get month's count ) as month
) t;

yes, u can do group by on dates to get todays nd months count. Hope this will give u some perception to go on.

user_63903640

Assume you want to query based on a particular date -

select count(case when d.date = :p_query_date then 0 end) day_count
      ,count(0) month_count
  from d -- your table name
 where d.date between date_trunc('month', :p_query_date) 
                  and date_trunc('month', :p_query_date + interval '1 month') - interval '1 day'

The above query assumes you have index defined on d.date column. If you have index defined on date_trunc('month', date), the query condition can be simplified to:

date_trunc('month', d.date) = date_trunc('month', :p_query_date)

user_25820500
Related