How to GROUP BY based on substring? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to GROUP BY based on substring?

Author

Topic

herman404
Starting Member

10 Posts

Posted - 2006-10-12 : 12:29:17

Hi everyone, here is a small problem I have. I have a query that does a sum and group by on several fields, and returns the following result set: (The GROUP BY is on field1 and field2, field3 is the SUM result)

field1 field2 field3
xxx FirstTestingString 1
xxx FirstString 2
yyy SecondTestingString 3
yyy SecondString 4

I want to further refine this query to produce the following result:
field1 field2 field3
xxx First 3
yyy Secon 7

Displaying the substring is not a problem, but I'm having trouble getting the records to aggregate based on a substring. Any ideas?

Thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-12 : 12:41:07

use derived table


select col1, substring(col2, ...), sum(tot_col3)
from
(
    select col1, col2, sum(col3) as tot_col3
    from   table
    group by col1, col2
) a
group by col1, substring(col2, ...)

KH

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-10-12 : 13:04:24

The key is that you have to use the expression SUBSTRING(col_name,x,y) in your GROUP BY clause. For example:

SELECT
		first_part	=	SUBSTRING(col1,1,5)
	,	second_part	=	SUBSTRING(col1,6,10)
	,	total		=	SUM(col2)
FROM
		table_name
GROUP BY
		SUBSTRING(col1,1,5)
	,	SUBSTRING(col1,6,10)

Does that solve your problem?

herman404
Starting Member

10 Posts

Posted - 2006-10-13 : 05:28:33

Hi everyone,

Thanks for the reply, I just tried this and noticed that we are selecting the full text of field2 elsewhere, so adding the substring does not work. But I have to include the field2 in the GROUP BY otherwise the query will not execute. Here is the full query, to make thing clearer, some of the constant values in the select statement are there to substitute as stored procedure parameters:

--------------------------------------------
SELECT
4 as SourceID,
MIN(p.EvaluationDate) as PositionDate,
p.SettlementDate,
MIN(f.FundName),
MIN(f.FundID),
1,
LEFT(p.Description,7) as SecurityName,
p.Description as LongName,
isnull(AVG(s.ID), 0) as SecurityID,
SUM(p.Quantity) as Position,
Min(p.AssetPrice),
AVG(p.Evaulation) as MarketValue

FROM NetAssetValue p,
FundMap f,
SecurityMap_E s
where p.EvaluationDate = '20060929'
and f.FundID = 1
and left(p.Description,7) *= s.EVLLuxCode
AND p.Asset LIKE 'CAT.%'

GROUP BY p.SettlementDate,p.Description
----------------------------------------------------------------
I added the substring to the group by clause, and it didn't aggregate the way I wanted. I then tried removing p.Description from the group by, but it didn't compile due to p.description being referenced elsewhere. What's the best way around this?

herman404
Starting Member

10 Posts

Posted - 2006-10-13 : 12:27:18

Hi Khtann, I tried nesting the SELECT statements, but unfortunately I got the same output from my last attempt

SELECT
4 AS SourceID,
MIN(A.PositionDate),
MIN(A.FundName),
MIN(A.FundID),
AVG(A.SectorID),
A.SecurityName,
A.SettlementDate,
ISNULL(AVG(A.SecurityID),0),
SUM(A.Position),
MIN(A.AssetPrice),
AVG(A.MarketValue)
FROM
(
select
4 as SourceID,
MIN(p.EvaluationDate) as PositionDate,
--NULL as TradeDate,
p.SettlementDate AS SettlementDate,
MIN(f.FundName) AS FundName,
MIN(f.FundID) AS FundID,
1 AS SectorID,
SUBSTRING(p.Description,1,7) as SecurityName,
p.Description as LongName,
AVG(s.ID) as SecurityID,
SUM(p.Quantity) as Position,
MIN(p.AssetPrice) AS AssetPrice,
AVG(p.Evaulation) as MarketValue

from NetAssetValue p,
FundMap f,
SecurityMap_EVL s
where p.EvaluationDate = '20060929'
and f.FundID = 1
and left(p.Description,7) *= s.EVLLuxCode
AND p.Asset LIKE 'CAT.%'

group by p.SettlementDate,p.Description
) A
GROUP BY A.SettlementDate,A.SecurityName

Any ideas if what I want to accomplish is possible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources