Grouping on other... - SQL Server Forums

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)

Grouping on other...

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-20 : 12:27:22

I have the following:


SELECT TOP10.SICDESCRIPTION,
       TOP10.TOTALACCOUNTS,
       TOP10.ACCOUNTMV,
       TOP10.ACCOUNTFEELY,
       TOP10.SICID,
       TOP10.RNUMBER
FROM   (SELECT   TOP 10 SICDESCRIPTION,
                        COUNT(* ) AS [TOTALACCOUNTS],
                        SUM(ACCOUNTMV) AS [ACCOUNTMV],
                        SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                        SICID,
                        COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    (BRANCHSTATE = 'Arizona')
                 AND SICDESCRIPTION IS NOT NULL
                 AND (MONTHEND = '06/01/2007')
                 AND SICID <> '00'
        GROUP BY SICID,SICDESCRIPTION,MONTHEND
        ORDER BY ACCOUNTMV DESC) AS TOP10
UNION 
SELECT OTHER.SICDESCRIPTION,
       OTHER.TOTALACCOUNTS,
       OTHER.ACCOUNTMV,
	   OTHER.ACCOUNTFEELY,
       OTHER.SICID,
       OTHER.RNUMBER
FROM   (SELECT   TOP 100 PERCENT SICDESCRIPTION,
                                 COUNT(* ) AS [TOTALACCOUNTS],
                                 SUM(ACCOUNTMV) AS [ACCOUNTMV],
                                 SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                                 SICID,
                                 COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    (BRANCHSTATE = 'Arizona') AND SICDESCRIPTION IS NOT NULL
          AND (MONTHEND = '06/01/2007') AND SICID <> '00' and SICDESCRIPTION NOT IN 
										(SELECT   TOP 10 SICDESCRIPTION
                                        FROM     SNAPSRAW
                                        WHERE    (BRANCHSTATE = 'Arizona')
                                                 AND SICDESCRIPTION IS NOT NULL
                                                 AND (MONTHEND = '06/01/2007')
                                                 AND SICID <> '00'
                                        GROUP BY SICID,SICDESCRIPTION,MONTHEND
)
		GROUP BY SICDESCRIPTION,SICID
        ORDER BY ACCOUNTMV DESC
) AS OTHER

It produces results like this:

What I would like to do is take all those other results and group them into a single line called other, and the sum total of accountmv. So basically in the end I will have the top 10, and than everything else wrapped up into 'other'. Just not sure how to go about putting together what I need

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 12:51:46

Provide the results of this:


SELECT   TOP 20 SICDESCRIPTION,
                        COUNT(* ) AS [TOTALACCOUNTS],
                        SUM(ACCOUNTMV) AS [ACCOUNTMV],
                        SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                        SICID,
                        COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    (BRANCHSTATE = 'Arizona')
                 AND SICDESCRIPTION IS NOT NULL
                 AND (MONTHEND = '06/01/2007')
                 AND SICID <> '00'
        GROUP BY SICID,SICDESCRIPTION,MONTHEND
        ORDER BY ACCOUNTMV DESC

Also, is SICDESCRIPTION the PK fo the table? SNAPSRAW?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-20 : 12:56:12

quote:

Originally posted by dinakar

Provide the results of this:


SELECT   TOP 20 SICDESCRIPTION,
                        COUNT(* ) AS [TOTALACCOUNTS],
                        SUM(ACCOUNTMV) AS [ACCOUNTMV],
                        SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                        SICID,
                        COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    (BRANCHSTATE = 'Arizona')
                 AND SICDESCRIPTION IS NOT NULL
                 AND (MONTHEND = '06/01/2007')
                 AND SICID <> '00'
        GROUP BY SICID,SICDESCRIPTION,MONTHEND
        ORDER BY ACCOUNTMV DESC

Also, is SICDESCRIPTION the PK fo the table? SNAPSRAW?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SICDESCRIPTION is not the PK and SNAPSRAW is a view

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 13:29:14

Then you have a chance of getting duplicate rows in the tOP20/other sections. Use the PK to get the top 20 and use the NOT IN clause to exclude those already in top 20.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-20 : 13:45:51

You mean the top 10? I guess I dont understand where you think I could get duplicates?

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 12:13:33

Ok, to clarify some things, I have snapsraw which is the table in which I am doing my logic from, snapsraw is really a view which looks like this:


SELECT     dbo.RS_CustomerValue.BankID, dbo.RS_CustomerValue.BranchID, dbo.RS_BranchCode.BranchState, dbo.RS_CustomerID.AccountID, 
                      dbo.RS_CustomerID.AccountName, dbo.RS_CustomerValue.InvestAuthID, dbo.RS_CustomerValue.RelationshipID, dbo.RS_Relationship.Company, 
                      dbo.RS_CustomerValue.AccountMV, dbo.RS_CustomerValue.AccountFeeTD, dbo.RS_CustomerValue.AccountFeeLY, 
                      dbo.RS_CustomerValue.CategoryID AS Category, dbo.RS_Category.Description AS CategoryDescription, 
                      dbo.RS_CustomerValue.StyleInvestID AS [Sales Officer], dbo.RS_CustomerValue.SicID, dbo.RS_SICSub_Group.Description AS SicDescription, 
                      dbo.RS_CustomerID.AccountZipCode, dbo.RS_CustomerValue.SectorID, dbo.RS_BusinessSector.Description AS SectorDescription, 
                      dbo.RS_CustomerValue.MonthEndID AS MonthEnd, dbo.RS_Month.Month, dbo.RS_Month.Year, dbo.RS_Month.Quarter, 
                      dbo.RS_BranchCode.BranchAbrev AS Babr, dbo.RS_CustomerValue.AdminID, dbo.RS_CustomerValue.OfficerID AS PM, 
                      dbo.RS_CustomerID.AccountOpenDate, dbo.RS_CustomerValue.SicIdDetail, dbo.RS_SICDeatil_Group.Description AS SicIdDetailDesc
FROM         dbo.RS_CustomerID INNER JOIN
                      dbo.RS_CustomerValue ON dbo.RS_CustomerID.AccountBankID = dbo.RS_CustomerValue.BankID AND 
                      dbo.RS_CustomerID.AccountID = dbo.RS_CustomerValue.AccountID INNER JOIN
                      dbo.RS_Month ON dbo.RS_CustomerValue.MonthEndID = dbo.RS_Month.MonthEndID LEFT OUTER JOIN
                      dbo.RS_SICDeatil_Group ON dbo.RS_CustomerValue.SicIdDetail = dbo.RS_SICDeatil_Group.SicGroupID LEFT OUTER JOIN
                      dbo.RS_BusinessSector ON dbo.RS_CustomerValue.SectorID = dbo.RS_BusinessSector.SectorID LEFT OUTER JOIN
                      dbo.RS_BranchCode ON dbo.RS_CustomerValue.BranchID = dbo.RS_BranchCode.BranchID LEFT OUTER JOIN
                      dbo.RS_Relationship ON dbo.RS_CustomerValue.RelationshipID = dbo.RS_Relationship.RelationshipId LEFT OUTER JOIN
                      dbo.RS_Category ON dbo.RS_CustomerValue.CategoryID = dbo.RS_Category.CategoryID LEFT OUTER JOIN
                      dbo.RS_SICSub_Group ON dbo.RS_CustomerValue.SicID = dbo.RS_SICSub_Group.SicsubID

I am currently doing the following to pull the top 10 we will call them categories:


SELECT   TOP 10 SICDESCRIPTION,
                        COUNT(* ) AS [TOTALACCOUNTS],
                        SUM(ACCOUNTMV) AS [ACCOUNTMV],
                        SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                        SICID,
                        COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    (BRANCHSTATE = 'Arizona')
                 AND SICDESCRIPTION IS NOT NULL
                 AND (MONTHEND = '06/01/2007')
                 AND SICID <> '00'
        GROUP BY SICID,SICDESCRIPTION,MONTHEND
        ORDER BY ACCOUNTMV DESC

I want to than add one more row to the top 10 that would than group all the remaining categories, and give me the sum value and assign it a category of "other". I've been struggling to find an example and was hoping someone would be kind enough to provide some guidance to a sql newbie.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:28:33

What is the PK in the table SNAPSRAW?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:29:40

Also pls post some sample data ( give us like 15 rows, showing how you want the groups/totals). I think I understand what you are trying to do.. some data will give me something to work on if I need to do a quick test on my local box.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 12:37:25

In this case this is my TOP10, and for the PK, since this is a view, I guess I didnt think it has a PK?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:40:52

The result might be from a view, but the base table must have a PK right? Or does it not have one? If it doesnt, its a bad bad design.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 12:43:14

The tables in which the view is built on has PK's in fact multiple of them, did you see the view design above? Not sure which table I need to tell you the PK on

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:45:39

quote:
Originally posted by dinakar

What is the PK in the table SNAPSRAW?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Please read my post above.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:46:33

quote:
Originally posted by duhaas

The tables in which the view is built on has PK's in fact multiple of them, did you see the view design above? Not sure which table I need to tell you the PK on

And you cannot have multiple PK's per table. A table can only have ONE Pk. Or did you mean multiple columns included in the PK?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 12:55:50

I did mean multiple columns in the PK, isnt that called a composite key? anyways, still struggling to tell you what the PK for snapsraw is, here is the sp help on it:

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 12:57:49

design of the view:

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 13:10:30

Try something like this:



Declare @t Table (rowId int identity, SICDESCRIPTION varchar(100), totalCounts int, SumAccount Decimal(10,2), SumAcctFee Decimal(10,2), SicId int, SumRelation int)
Insert into @t 
SELECT   
	<columns>
FROM dbo.SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'


Select 	
	SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
		And Rowid <= 10
GROUP BY SICID,SICDESCRIPTION,MONTHEND
--ORDER BY ACCOUNTMV DESC
Union ALL 
Select 'Other',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
		And Rowid > 10
GROUP BY SICID,SICDESCRIPTION,MONTHEND
ORDER BY ACCOUNTMV DESC

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 13:22:15

You can also do something like this:


Declare @top10  table () 
Insert into @top10 ( )
SELECT   TOP 10 SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
ORDER BY ACCOUNTMV DESC


Select <columns>
FROM @top10 
UNION ALL
SELECT   SICDESCRIPTION ='Others',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
		AND SICDESCRIPTION IS NOT NULL
		AND (MONTHEND = '06/01/2007')
		AND SICID <> '00'
	AND NOT EXISTS ( SELECT  <columns> FROM @top10 )
GROUP BY SICID,SICDESCRIPTION,MONTHEND
ORDER BY ACCOUNTMV DESC

Only include the columns that are in the GROUP BY when you check for NOT EXISTS.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 13:32:27

In the home stretch, I might not be understanding it all:

Declare @top10  table () 
Insert into @top10 ( )
SELECT   TOP 10 SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
ORDER BY ACCOUNTMV DESC


Select SICDESCRIPTION, COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
FROM @top10 
UNION ALL
SELECT   SICDESCRIPTION ='Others',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
		AND SICDESCRIPTION IS NOT NULL
		AND (MONTHEND = '06/01/2007')
		AND SICID <> '00'
	AND NOT EXISTS ( SELECT  SICID,SICDESCRIPTION FROM @top10 )
GROUP BY SICID,SICDESCRIPTION
ORDER BY ACCOUNTMV DESC

I get his message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'FROM'.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 14:16:36

I was hoping you can fill the blanks..


Declare @top10  table (SICDESCRIPTION varchar(100),  TotCount int, SumACc decimal(10,2), SumAccFee decimal(10,2), SicId int, RNum int)
Insert into @top10

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-25 : 14:29:17

Sorry, not familiar with this route your taking, although excited about it. Now I have the following:


Declare @top10  table (SICDESCRIPTION varchar(100),  TotCount int, SumACc decimal(10,2), SumAccFee decimal(10,2), SicId int, RNum int)
Insert into @top10 
SELECT   TOP 10 SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
ORDER BY ACCOUNTMV DESC


Select SICDESCRIPTION,TotCount,SumACc,SumAccFee,SicId,RNum
FROM @top10 
UNION ALL
SELECT   SICDESCRIPTION ='Others',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
		AND SICDESCRIPTION IS NOT NULL
		AND (MONTHEND = '06/01/2007')
		AND SICID <> '00'
	AND NOT EXISTS ( SELECT  SICID,SICDESCRIPTION FROM @top10 )
GROUP BY SICID,SICDESCRIPTION ORDER BY ACCOUNTMV DESC

and get

Msg 8120, Level 16, State 1, Line 2
Column 'SNAPSRAW.SicDescription' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'ACCOUNTMV'.
Msg 104, Level 16, State 1, Line 32
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Like I said, i appreciate the advice on all this, and it will be of great assistance to me

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 14:36:24

[code]
Declare @top10 table (SICDESCRIPTION varchar(100), TotCount int, SumACc decimal(10,2), SumAccFee decimal(10,2), SicId int, RNum int)
Insert into @top10
SELECT TOP 10 SICDESCRIPTION,
COUNT(* ) AS [TOTALACCOUNTS],
SUM(ACCOUNTMV) AS [ACCOUNTMV],
SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
SICID,
COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM SNAPSRAW
WHERE (BRANCHSTATE = 'Arizona')
AND SICDESCRIPTION IS NOT NULL
AND (MONTHEND = '06/01/2007')
AND SICID <> '00'
GROUP BY SICID,SICDESCRIPTION
ORDER BY ACCOUNTMV DESC

Select SICDESCRIPTION,TotCount,SumACc,SumAccFee,SicId,RNum
FROM @top10
UNION ALL
SELECT SICDESCRIPTION ='Others',
COUNT(* ) AS [TOTALACCOUNTS],
SUM(ACCOUNTMV) AS [ACCOUNTMV],
SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
SICID,
COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
FROM SNAPSRAW
WHERE (BRANCHSTATE = 'Arizona')
AND SICDESCRIPTION IS NOT NULL
AND (MONTHEND = '06/01/2007')
AND SICID <> '00'
AND NOT EXISTS ( SELECT SICID,SICDESCRIPTION FROM @top10 )
GROUP BY SICID,SICDESCRIPTION ORDER BY ACCOUNTMV DESC

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources