SQL Group by date and payment total

Question

I'm trying to pull data from a payment table. I need the total Payments received per day. So The column names are "pmttotamt" and [date].

I've tried variations of this statement but am getting 518 rows back. There are only 31 days in the month of JAN

select
  cast(date as char(11)),
  sum(pmttotamt)
from pmts
where year(date) = 2025
  and month(date) = 1
group by [date],(pmttotamt)

Answer 1

Looks like you need to group by just the date component. So cast to date, or use the new, more efficient, DATETRUNC function.

Also use date ranges not functions in the WHERE.

select
  cast(pmts.date as date),
  sum(pmts.pmttotamt)
from pmts
where pmts.date >= '20250101'
  and pmts.date <  '20250201'
group by cast(pmts.date as date);
-- alternatively
group by datetrunc(day, pmts.date);