sql script: is this possible (multiple groups)

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 2005 Forums

Transact-SQL (2005)

sql script: is this possible (multiple groups)

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2007-06-15 : 15:09:45

Hi,
I have a query request but I'm not sure it is possible via a sql script. Essentially a report that has multiple group breakdowns.

Essentially after each unique agency, I need to display a summary for the total premium. If you run the below script you'll see it returns a summary for each user per agency. I then need to have a summary line for each agency (see the "all" lines):


AgencyCode           UserID      QuoteType            TotalPremium           
-------------------- ----------- -------------------- ------------------------ 
agency1              1           auto                 1500
agency1              1           home                 900
agency1              2           auto                 1200
agency1		     all	 all		      3600
agency2              3           auto                 2500
agency2              3           homw                 1000
agency2              4           home                 400
agency2		     all         all		      3900

Can this be done via just sql?


create table test
(
	userID int
	,AgencyCode varchar(20)
	,QuoteType varchar(20)
	,TotalPremium float
)

insert into test select 1,'agency1','auto',1000
insert into test select 1,'agency1','auto',500
insert into test select 1,'agency1','home',700
insert into test select 1,'agency1','home',200
insert into test select 2,'agency1','auto',1000
insert into test select 2,'agency1','auto',200
insert into test select 3,'agency2','auto',1000
insert into test select 3,'agency2','auto',1500
insert into test select 3,'agency2','homw',1000
insert into test select 4,'agency2','home',400


select 
	AgencyCode
	,UserID
	,QuoteType
	,SUM(TotalPremium) AS TotalPremium
from
	test
Group BY
	AgencyCode
	,UserID	
	,QuoteType

drop table test

Nic

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2007-06-15 : 15:14:56

You might be able to do this with the ROLLUP statement in the group by clause. Look it up in Books on Line.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-15 : 15:48:04

will this do?


CREATE TABLE test
(
	userID INT
	,AgencyCode VARCHAR(20)
	,QuoteType VARCHAR(20)
	,TotalPremium FLOAT
)

INSERT INTO test SELECT 1,'agency1','auto',1000
INSERT INTO test SELECT 1,'agency1','auto',500
INSERT INTO test SELECT 1,'agency1','home',700
INSERT INTO test SELECT 1,'agency1','home',200
INSERT INTO test SELECT 2,'agency1','auto',1000
INSERT INTO test SELECT 2,'agency1','auto',200
INSERT INTO test SELECT 3,'agency2','auto',1000
INSERT INTO test SELECT 3,'agency2','auto',1500
INSERT INTO test SELECT 3,'agency2','homw',1000
INSERT INTO test SELECT 4,'agency2','home',400


SELECT	AgencyCode
		,UserID
		,QuoteType
		,TotalPremium
FROM
(
	SELECT 
		1 AS ordinal
		,AgencyCode
		,UserID
		,QuoteType
		,SUM(TotalPremium) AS TotalPremium
	FROM	test
	GROUP BY AgencyCode, UserID, QuoteType
	UNION ALL
	SELECT	2 AS ordinal
		, AgencyCode
		, -1 -- convert this to ALL in your front end
		, 'all'
		, SUM(TotalPremium) AS TotalPremium
	FROM	test
	GROUP BY AgencyCode
) t1	
ORDER BY AgencyCode, ordinal

DROP TABLE test

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

nic
Posting Yak Master

209 Posts

Posted - 2007-06-15 : 16:14:50

Wow, ROLLUP Rocks! Never knew about that one.

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources