By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

"group by" by excluding prefix

P: n/a
Suppose there is a table containing these recodes.

country
-------
CON_CHN
CON_JAP
JAP
CON_CHN

When I use the following sql:
select country, count(*) as num from table group by country

the normal result will be:
country num
---------------
CON_CHN 2
CON_JAP 1
JAP 1

However, my desired result is as follows:
country num
-----------------
CON_CHN 2
CON_JAP 2

How can I re-write my SQL? Or any other methods to do that?

Apr 12 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
MC
Is the length of the string (prefix or sufix) fixed? Can the string be
something like CON_CANA ? Also, it would help if you provided sample
scripts....
MC
"littlebeam" <cy*******@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Suppose there is a table containing these recodes.

country
-------
CON_CHN
CON_JAP
JAP
CON_CHN

When I use the following sql:
select country, count(*) as num from table group by country

the normal result will be:
country num
---------------
CON_CHN 2
CON_JAP 1
JAP 1

However, my desired result is as follows:
country num
-----------------
CON_CHN 2
CON_JAP 2

How can I re-write my SQL? Or any other methods to do that?

Apr 12 '06 #2

P: n/a
If your prefix is always "CON_" then this should work

SELECT Replace(country, 'CON_',''), COUNT(Replace(country, 'CON_',''))
from myTable
Group by Replace(country, 'CON_','')

Markus

Apr 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.