Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Microsoft SQL Query with Sub Totals 1

Status
Not open for further replies.

Divaldo

Technical User
Sep 18, 2007
18
GB
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.
 
Try:

Code:
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]
 
Perfect!!!

Thank you so much! Nice and simple too.
 
Is there any way to label the rows with the totals on so it's clear that they are displaying a total?
 
Try something like this:

Code:
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]
 
Once again, you are my hero.

Thank you, that works great.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top