GROUPing BY a substring field ? how to!

[JD1958]

I'd like to group a field to which ive taken a substring of another field, but when i cant group by this substring that is in my select statement, any posible solutions ???? this is the simpliefied query with all the non essential stuff taken out

SELECT SUBSTR(FIELDX,3,7),COUNT(*)
FROM DB
WHERE various condition
GROUP BY SUBSTR(FIELDX,3,7)

field x is a brutally long field to which i only need a 7 byte field .... so how do i group this and only hav that substring displayed and counted

thanks !

 

[dnoeth]

SELECT XXX,COUNT(*)
FROM
(SELECT SUBSTR(FIELDX,3,7) as XXX
FROM DB
WHERE various condition
)
GROUP BY XXX

Dieter

 

[swampBoogie]

SELECT c1,count(*) from 
(select
SUBSTR(FIELDX,3,7) as c1      
FROM DB
WHERE various condition                                                              ) as dt
GROUP BY c1

 

[fredericofonseca]

with tbl1 (newfield) as (SELECT SUBSTR(FIELDX,3,7) FROM DB
WHERE various condition)
select newfield from tbl1
group by newfield


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JD1958]

Thanks for the following advice to all , the first 2 replies seemed to have a commonality so i tried that first but for some reason its returning back the following syntax error, i will include my entire query

ughh ... help !!!!!

SELECT LOCDIST,COUNT(*) FROM
(
SELECT SUBSTR(PAYCHECK_DIST_KEY1,3,7) AS LOCDIST
FROM HRP.PS_PAY_CHECK
WHERE PAY_END_DT IN ('2003-08-31','2003-08-24')
AND SUBSTR(PAYCHECK_DIST_KEY1,1,1) = 'C'
AND LOCATION IN ('LOC5064','LOC1237')
)
GROUP BY LOCDIST
QUERY MESSAGES:
SQL error at or before <EMPTY> (line 8, position 2). )

 

[JamesLean]

You have to give derived tables an alias, so this would become:

SELECT LOCDIST, COUNT(*)
FROM
(
SELECT SUBSTR(PAYCHECK_DIST_KEY1,3,7) AS LOCDIST
FROM HRP.PS_PAY_CHECK
WHERE PAY_END_DT IN ('2003-08-31','2003-08-24')
  AND SUBSTR(PAYCHECK_DIST_KEY1,1,1) = 'C'
  AND LOCATION IN ('LOC5064','LOC1237')
) dt
GROUP BY LOCDIST

Just out of interest, have you tried simply:

SELECT SUBSTR(PAYCHECK_DIST_KEY1,3,7) AS LOCDIST, COUNT(*)
FROM HRP.PS_PAY_CHECK
WHERE PAY_END_DT IN ('2003-08-31','2003-08-24')
  AND SUBSTR(PAYCHECK_DIST_KEY1,1,1) = 'C'
  AND LOCATION IN ('LOC5064','LOC1237')
GROUP BY SUBSTR(PAYCHECK_DIST_KEY1,3,7)

--James

 

[JD1958]

ahhhh the alias ........ thats the ticket , now it works and yes i had tried your second suggestion but apparantly you can't do a substr in a group by statement ...oddly enough !


thanks !!! to all .... all is good now in db2 land !

 

[fredericofonseca]

Just out of curiosity have you tried &quot;my&quot; way?


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

frederico, nice syntax, i've never seen that before

but then, i don't use the new-fangled syntax if the old style works so nicely

besides, you never know which version of which database supports the new syntax...

at least the derived table approach is fairly safe

in fact, i like james' even simpler solution, and i have a hard time believing you cannot use a substring expression in GROUP BY

by the way, frederico, you forgot to COUNT(*) ;-)

rudy

 

[swampBoogie]

The ANSI SQL 99 specification does not allows for expressions in a group by clause.

 

[r937]

thanks swampboogie

that's helpful to know -- let's hope i can remember it, eh

;-)