groupings - 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

Transact-SQL (2000)

groupings

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-24 : 12:11:27

We have some data which at the moment is sorted by an account code

ACC001 FRANSON LTD
ACC002 FRANSON LIMITED
ACC003 JONES LTD
ACC004 JENSON PLC

We total up invoices and group by the account code in the same table
select sum(invoices), accountcode
from table group by accountcode

What we would like to do is give similar companies a parent code and group by that instead except the parent code is going to be in a different table

ACC001 ACC001 FRANSON LTD
ACC001 ACC002 FRANSON LIMITED
ACC003 ACC003 JONES LTD
ACC004 ACC004 JENSON PLC

How would this be done? Inner join on the accountcode between the tables but how do you then group by the parent instead and use the account name from the original table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 13:01:42

You can store the accountcode and parentcode to parent table. join this with original table on account code. then group on parentcode and return the sum(invoices). you could even add a parentgroupname field in parent table(ex. FRANSON as parent group for FRANSON LTD & FRANSON LIMITED)

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-29 : 04:52:37

Does not seem to be picking up the extras:
I think it is because I have left the group by companyname in there but how else do I display the different companyname in the result set?


declare @topnum int,
@month int
set @topnum = 500
set @month = 0

SET ROWCOUNT @topnum

SELECT 		C.[ID],
		--C.AccountCode,
		C.ParentGroup,
		C.CompanyName,
		sum([amtin loccur]) AS Turnover
FROM 		zarinvreg
LEFT JOIN	Companies C ON zarinvreg.Customer = C.AccountCode
WHERE 		monthdata >= @month-11 --this gives us 12 months
AND 		[name 1] <> '-'
AND 		[name 1] is not null
GROUP BY 	C.ID, C.ParentGroup, --C.AccountCode, 
		C.CompanyName
ORDER BY 	Turnover desc

SET ROWCOUNT 0

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-29 : 05:06:45

Ok, corrected, but I need to get the companyname back in here and only one of the rows where the parent name is the same:


declare @topnum int,
@month int
set @topnum = 500
set @month = 0

SET ROWCOUNT @topnum

SELECT 		C.ParentGroup,
		sum([amtin loccur]) AS Turnover
FROM 		zarinvreg
LEFT JOIN	Companies C ON zarinvreg.Customer = C.AccountCode
WHERE 		monthdata >= @month-11 --this gives us 12 months
AND 		C.CompanyName <> '-'
AND 		C.CompanyName is not null
GROUP BY 	C.ParentGroup
ORDER BY 	Turnover desc

SET ROWCOUNT 0

Example data I need to avoid:
ParentGroup AccountCode Name
ZINT0101 ZINT0101 FRANSON LTD
ZINT0101 ZINT0101 FRANSON LIMITED
ZINT0101 ZINT3456 FRANSON BUILDINGS LTD.

It must only pick up one of the names of the first 2 while still grouping on ZINT0101

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources