dnnstuffadmin posted on February 17, 2010 11:30
I received an inquiry from a user who wanted to do a simple column total with SQLView Pro and thought I'd post here in case anyone else could use the info. There are much more complicated methods to do this type of thing but I try to use the simplest method that will get the work done. In this case I'm using the sample data installed with SQLView Pro, namely the DNNStuff_SQLViewPro_Sample_Budgets table.
SELECT
CAST(Year as varchar) Year, Amount
FROM
dbo.DNN_DNNStuff_SQLViewPro_Sample_Budgets
WHERE DepartmentId = 1001
UNION
SELECT
'Total' Year, sum(Amount) Amount
FROM
dbo.DNN_DNNStuff_SQLViewPro_Sample_Budgets
WHERE DepartmentId = 1001
This ends up producing a simple table with two columns, year and amount. I've cast the year column to varchar so we can use a text description for the 'total' line.
Year Amount
2001 408992.00
2002 416367.00
2003 648695.00
2004 214150.00
2005 610999.00
2006 254314.00
2007 622206.00
2008 434410.00
2009 169988.00
2010 329782.00
2011 585266.00
2012 835103.00
Total 5530272.00
This method won't work for all the times you want totals, such as subtotalling on breaks etc. but it's easy to do for simple queries like the one above.