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 开发者_StackOverflowJOIN
                  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

"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)

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

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