Add sumation to the end of query result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Add sumation to the end of query result

Author

Topic

hamidm
Starting Member

2 Posts

Posted - 2004-08-09 : 04:51:05

Hi,

I want to add sumation to a query result in ONE statement. for ex:

f1
---
1
2
3
4
5
Sum=15

does anyone have any idea?

thanks,
/Hamid

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-09 : 05:08:23

This is something you are better off doing at the presentation layer. Front-end tools especially reporting software can do this much better at that end, doing this in SQL can be tricky and messy.

If you still need to do it in SQL Server - there are many ways to do it in SQL, just depends on what suits you best. Do you have any grouping in the statement? If you do then WITH ROLLUP is the easiest way of doing it:


select orderid, sum(unitprice) from [order details] where orderid < 10260 
group by orderid with rollup

If you dont, you can use the COMPUTE clause to return the Grand total, but that will be returned as a different resultset:

 
select unitprice from [order details] where orderid < 10260 
compute sum(unitprice)

The best way, in my opinion is to do use a UNION with an aggregation query:


select orderid, unitprice from [order details] where orderid < 10260 
UNION ALL
select NULL, SUM(unitprice) from [order details] where orderid < 10260

hamidm
Starting Member

2 Posts

Posted - 2004-08-09 : 05:43:15

hi,

Thanks. UNION ALL with using NULL works fine.

many thanks, :-)
/Hamid

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 12:06:15

This sounds like a poster child for a COMPUTE clause, something like:

SELECT Sum(i.rows), u.name
   FROM dbo.sysindexes AS i
   JOIN dbo.sysusers AS u
      ON (u.uid = i.uid)
   GROUP BY u.name
   ORDER BY i.rows DESC, u.name
   COMPUTE Sum(Sum(i.rows))

-PatP

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-10 : 01:25:36

Pat, I did give him that option as well in my post, but COMPUTE is usually a little tricky for many people to use on the application end since it is returned as another recordset. Funnily, the Books Online mentions that COMPUTE and COMPUTE BY are included only for backward compatibility, it recommends using Analysis Services (eeks!) or the ROLLUP operator. Obviously, nobody told them ROLLUP doesn't work without a GROUP BY.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources