Link to home
Start Free TrialLog in
Avatar of thomasgroup
thomasgroupFlag for United States of America

asked on

Grand total needed row - SQL

I would like a row at the bottom of my query results called "GrandTotal" which sums the Quantity, Amount and Total columns independently.

There are more than five records in the db but attached is an example of what I would like the query results return.  The table name is vInventory

Thanks!
GoalSnapShot.gif
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Typically this kind of functionality would be handled at the application level.  As an alternative try...

SELECT ... --Existing Query
UNION
SELECT 'Grand Total', SUM(Quantity), SUM(Amount), SUM(Total)
FROM ...
Is there a GROUP BY in the query that produces the results?  If so, you can use WITH ROLLUP to generate the total, and it will be part of the result set.

You also could use COMPUTE; be aware, though, that this means two different result sets are generated: the detail set, and the total set.

SELECT ...
FROM ...
WHERE ...
COMPUTE SUM(Quantity), SUM(Amount), SUM(Total)
vendor, quantity, amount, total

declare @table table(vendor vachar(20), quantity INT, amount MONEY, total MONEY)

insert into @table
select vendor, quantity, amount, money
from table

select * from @table
UNION
select 'grant total', sum(quantity), sum(amount), sum(total)
from @table
Avatar of thomasgroup

ASKER

BriCrowe AND chapmandew,
Either of these work well, how now would I sort the data by vendor name and still have the grandtotal appear at the bottom of the page?
SnapResults.gif
declare @table table(vendor vachar(20), quantity INT, amount MONEY, total MONEY, sorter int)

insert into @table
select vendor, quantity, amount, money, 1 as sorter
from table

select * from
(
select * from @table
UNION
select 'grant total', sum(quantity), sum(amount), sum(total), 999999
from @table
)
order by sorter, vendor
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial