I have a query that selects counts group by department.
select department, count(*) group by department
This returns two columns data result. I wonder if select cout, count by today and count by this month same query.
Department Total Today ThisMonth
AAA 40 8 23
BBB 20 6 11
Try This
select department,count(*),
sum(case when datecolumn= GETDATE() then 1 else 0 end) as Today,
sum(case when month(datecolumn) =month(GETDATE() and year(datecolumn) =year(GETDATE()) then 1 else 0 end) as ThisMonth
from YourTable
group by department
Comparison of datecoulmn with getdate() should be by MMYYYY to get ThisMonth total.
select department,count(*) as Total,
sum(case when convert(varchar,datecolumn,106)= convert(varchar,GETDATE(),106) then 1 else 0 end) as Today,
sum(case when cast(month(datecolumn) as varchar) + cast(year(datecolumn) as varchar) = cast(year(GETDATE()) as varchar) + cast(year(GETDATE()) as varchar) then 1 else 0 end) as ThisMonth
from YourTable
group by department
select
department,
count(*) as total,
count(*) filter (where datecolumn = current_date) as count_for_today,
count(*) filter (where date_trunc('month', datecolumn) = date_trunc('month', current_date)) as count_for_month
from
your_table
group by
department
@SimarjeetSingh has already answered this.
except the comparison with datecolumn should happen with datepart only, GETDATE() retuns time with it.
select department, count(*) as Total,
sum(case when cast(datecolumn as date)= cast(GETDATE() as date) then 1 else 0 end) as Today,
sum(case when month(datecolumn) =month(GETDATE()) and year(datecolumn) = year(GETDATE()) then 1 else 0 end) as ThisMonth
from YourTable
group by department
