SQL Query - Group and SUM of Values

Question

I have the following database structure:

ID | Payment (Decimal) | PaymentDate (DateTime) | PaymentStatus(int)

I am currently able to get a grouping of all of the payments over time by Year and Date and get the total across all Payment Status's using the following query;

Select 
YEAR = YEAR(DueDate),
MONTH = MONTH(DueDate),
MMM = UPPER(left(DATENAME(MONTH,DueDate),3)),
Totals = sum(Payment)
from 
PaymentSchedules
Where DueDate IS NOT NULL
Group by 
YEAR(DueDate),
MONTH(DueDate),
DATENAME(Month,DueDate)
Order By
YEAR,
MONTH

This gives me the results so far so good.

What I would like to be able to do is have added totals for the splits in each section. So for example if each payment could be Paid (1) or Unpaid (2) or Overdue (3) I would like to not only get the number of paid / unpaid / overdue but I would also like to get the total value of unpaid items / paid items / Overdue items for each Year / Month combination.

sstan sstan · Accepted Answer · 2015-08-07T20:35:31

You just need to add SUMs with CASE statements inside to only sum payments when the correct status is detected, like this:

Select YEAR = YEAR(DueDate),
       MONTH = MONTH(DueDate),
       MMM = UPPER(left(DATENAME(MONTH,DueDate),3)),
       TotalPaid = sum(case when PaymentStatus = 1 then Payment else 0 end),
       TotalUnpaid = sum(case when PaymentStatus = 2 then Payment else 0 end),
       TotalOverdue = sum(case when PaymentStatus = 3 then Payment else 0 end),
       Totals = sum(Payment)
from PaymentSchedules
Where DueDate IS NOT NULL
Group by YEAR(DueDate),
         MONTH(DueDate),
         DATENAME(Month,DueDate)
Order By YEAR,
         MONTH

SQL Query - Group and SUM of Values

2 Answers