Thread: SQL Help- Grand Total

Because you are grouping by Location/FirstName/Surname you will only get totals for these lines.

If you want grand totals, you will have to have a separate clause that does no grouping.

What are you using to report this? (e.g. Crystal Reports etc). Can you not use the reporting tool to do the summing of the detail lines?

Note that this query will give you unexpected results if you have 2 persons at the same location with the same names, as it will combine them.

As spudboy said add the uique id of the employees to prevent those with the same name being combied.

You want to group by location for the sum. SQL won't like that so the best trick is to do it in the reports program or have a seperate SQL statement to do the grand total.

What is your reporting tool?

Oh
You could also play with a nested select.

Thanks for your replies, I am writing it in Management Studio for our payroll system, the @[Last Pay Period End Date] is the way MicrOpay needs it so the report can have parameters.

I will look into 'Nested Select' Not sure how that works.

The 'With Rollup' gives me a subtotal under each employee. The 'With Cube' just makes a mess

I wish I could write this in Crystal....

I will keep you posted with the outcome.... but if anyone comes up with a idea, please let me know

Dorko

It's a bit ugly, but if you were desperate you could UNION the query above with one that returned

...
UNION
Null, Null, Null, Sum(Blah blah blah...)

or

...
UNION
'Total', 'Total', 'Total', Sum(Blah blah blah...)

Where the first three fields filled in the Group By values of Locn/FN/Surname.

As an aside, I guess you don't have control of the schema, so the field names with spaces in them probably come from MicrOPay, but everything is much neater if you don't put spaces in the field names.

Thanks, i will try for using a union...

Dorko

I've had another thought!! Dangerous, I know.

You could do your original view with the rollup clause, which gives you too many levels of subtotal, as previously mentioned.

However, you could build another view on that view that took all of the rows with data values in the first three fields, but omitted rows with Null in the Surname/Firstname, apart from the last row which would have null in all 3 columns, sort of like this:

Select * from OriginalView
Where Surname is not Null -- This gets the normal lines
or (Location is Null and FirstName is Null and Surname is Null) -- This gets the grand total line

Maybe that would be easier?

Cheers
David

Hmmm.... i see where you are going with this, but how will i tell it to keep the last line?

I will try this out on the weekend...At the moment i told the client cant be done, but i will research it further.

Thanks mate, could be on something!

Dorko

Well, the last line in a rollup has all null values (apart from the values you are summing), so you can pick the last line by asking for all the "Group by" fields to be null.

If I assume you have a single field for firstname/Surname, your Location/name results rollup query will give you something like this:
East, Fred Smith, 1111, 1111
East, Mary Jones, 2222, 2222
East, NULL, 3333, 3333
West, David Jones, 2222, 2222
West, Jenny Jones, 3333, 3333
West, NULL, 5555, 5555
NULL, NULL, 8888, 8888

If you then use this as input to another query/view, and filter for the Name field to be not null, except where the location field is null you would get this recordset:

East, Fred Smith, 1111, 1111
East, Mary Jones, 2222, 2222
West, David Jones, 2222, 2222
West, Jenny Jones, 3333, 3333
NULL, NULL, 8888, 8888

What do you think of that solution?