Microsoft SQL Query with Sub Totals 1

[Divaldo]

I have created an InfoPath form that allows users to submit jobs to our Reprographics department and it tallies up the cost of their job so it can be billed to their department at the end of the month.

Each department (faculty) has multiple sub faculties.

The query I use to get the monthly results is:

SELECT FacultyName, SubFacultyName, Totalprice
From Jobs
Where Year(DateSubmitted) = Year(CURRENT_TIMESTAMP)
AND Month(DateSubmitted) = Month(CURRENT_TIMESTAMP)

Order by FacultyName, SubFacultyName

This produces three columns. One showing the faculty, the second showing the subfaculty and the third showing the cost. There is a row for each job that has been submitted.

Something like this:

Facultyname Subfacultyname Totalprice
Dev Dev1 0.01
Dev Dev1 0.23
Dev Dev2 1.32
Dev Dev3 2.11
Research Res1 0.12
Research Res1 0.02


Can anyone guide me (I am a complete beginner!) with regard to adding subtotals in? Ideally I'd like a Subtotal on a separate line each time the SubFaculty changes in the results. So in the example above I'd have subtotals for Dev1, Dev2, Dev3 and Res1.

Thanks.

 

[imex]

Try:

SELECT FacultyName, SubFacultyName, sum(Totalprice) as Total
From Jobs
Where Year(DateSubmitted) = Year(CURRENT_TIMESTAMP) 
AND Month(DateSubmitted) = Month(CURRENT_TIMESTAMP)
Group By Grouping Sets ( (FacultyName, SubFacultyName), (FacultyName), () )

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Divaldo]

Perfect!!!

Thank you so much! Nice and simple too.

 

[Divaldo]

Is there any way to label the rows with the totals on so it's clear that they are displaying a total?

 

[imex]

Try something like this:

SELECT 
    case when GROUPING_ID(FacultyName, SubFacultyName) = 3 then 'Total' else FacultyName end as FacultyName, 
    case when GROUPING_ID(FacultyName, SubFacultyName) = 1 then 'Total Faculty' else SubFacultyName end as SubFacultyName, 
    sum(Totalprice) as Total
From Jobs
Where Year(DateSubmitted) = Year(CURRENT_TIMESTAMP) 
AND Month(DateSubmitted) = Month(CURRENT_TIMESTAMP)
Group By Grouping Sets ( (FacultyName, SubFacultyName), (FacultyName), () )

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Divaldo]

Once again, you are my hero.

Thank you, that works great.