CODEWITHSUNDEEP
sql-server-2008
giles
giles

Reputation: 843

SQL SUM() in a month on month report

I have the following that returns the total invoiced amount for a given type of job. I now want to break it down into monthly totals. Any tips on how I should approach this 

SELECT     dbo.jm_job_type.job_type_desc, SUM(dbo.jm_invoice.invoice_amount) AS 'inv tot'
FROM         dbo.jm_invoice INNER JOIN
                  dbo.jm_job ON dbo.jm_invoice.job_no = dbo.jm_job.job_no INNER JOIN
                  dbo.jm_job_type ON dbo.jm_job.job_type_no = dbo.jm_job_type.job_type_no
GROUP BY dbo.jm_job_type.job_type_desc

Upvotes: 3

Views: 506

Answers (3)

KM.
KM.

Reputation: 103579

"floor" to the Month (most efficient way to get month+year only) and then GROUP BY that"

GROUP BY DATEADD(month,DATEDIFF(month,0, dbo.jm_invoice.YourDate),0)

so it would be:

SELECT     dbo.jm_job_type.job_type_desc,
           DATEADD(month,DATEDIFF(month,0, dbo.jm_invoice.YourDate),0) AS MonthYear,
           SUM(dbo.jm_invoice.invoice_amount) AS 'inv tot'
FROM         dbo.jm_invoice INNER JOIN
                  dbo.jm_job ON dbo.jm_invoice.job_no = dbo.jm_job.job_no INNER JOIN
                  dbo.jm_job_type ON dbo.jm_job.job_type_no = dbo.jm_job_type.job_type_no
GROUP BY dbo.jm_job_type.job_type_desc, 
         DATEADD(month,DATEDIFF(month,0, dbo.jm_invoice.YourDate),0)

Upvotes: 1

Paul Sasik
Paul Sasik

Reputation: 81429

Use the SQL Server datepart function on the date in question in your Group By like this:

GROUP BY 
    DatePart(yy, dbo.jm_invoice.some_date), 
    DatePart(mm, dbo.jm_invoice.some_date), 
    dbo.jm_job_type.job_type_desc

Upvotes: 1

Mike Mooney
Mike Mooney

Reputation: 11989

Add the year and month field to your SELECT list, and also add it to your GROUP BY list

Upvotes: 0

Related Questions