Suppressing column data and calculating subtotal

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

Transact-SQL (2000)

Suppressing column data and calculating subtotal

Author

Topic

skchaman
Starting Member

6 Posts

Posted - 2004-10-16 : 04:32:01

Hi I used the following in my query

select
id = case when name = (select min(name) from t t2 where t.id = t2.id) then id else null end ,
name, Qty
from t
order by id, name

and its working fine and giving me an output as follows

GroupID UserName Qty
------- -------- -------
100 Alan 100
Bob 100
Craig 100
200 Daniel 200
Mike 300

Now I want My Out put as follows:

GroupID UserName Qty
------- -------- -------
100 Alan 100
Bob 100
Craig 100

Subtotal 300

200 Daniel 200
Mike 300

Subtotal 500

Grand Total 800

Can Anybody Help

Thanks

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 05:00:35

select
id = case when name = (select min(name) from t t2 where t.id = t2.id) then id else null end ,
name,
SUM(Qty)
from t
GROUP BY
case when name = (select min(name) from t t2 where t.id = t2.id) then id else null end,
name
WITH ROLLUP
order by id, name

Kristen

skchaman
Starting Member

6 Posts

Posted - 2004-10-16 : 08:21:53

Dear Kristen,
Thanks for the promp posting of reply, but it gives error while running in query analyser as follows;

Cannot use an aggregate or a subquery in an expression used for the group by list of a group by clause.

Please sugest

Thanks

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 10:21:19

I suppose you can't have the subselect in there with the GROUP BY. Convert that a JOIN instead.

Here's an example


USE Northwind
GO
SELECT	CustomerID,
	ProductID,
	SUM(Quantity)
FROM	dbo.Orders O
	JOIN dbo.[Order Details] OD
		ON O.OrderID = OD.OrderID
GROUP BY CustomerID, ProductID
	WITH ROLLUP
ORDER BY CustomerID, ProductID

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-16 : 11:52:01

presentation layer

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources