Count records with soundex and inner join!

[KamranFeroz]

Hi,

I'm trying to count no. of records using soundex() function in query,

Query:
"SELECT COUNT(*) AS Expr1, a.Country AS Expr2
FROM tblSurvey a INNER JOIN
tblSurvey b ON SOUNDEX(a.Country) = SOUNDEX(b.Country)
GROUP BY a.Country
ORDER BY a.Country"

I'm not getting the right no. of records with the above query. e.g. I've 1 rec. of "Baharain" and 28 recs. of "Bahrain", but query shows Baharain=29 and Bahrain=812. same is the case with the other records.

Any suggestion ?

Cheers!


Web Programmer.

http://www.kamranferoz.com

 

[vongrunt]

SOUNDEX() produces n:n relationship. (1*(1+28) = 29, 28*(1+28)=812)

Try with SOUNDEX() in GROUP BY... but then you must choose only one scalar value to display (either Bahrain or Baharain). Something like:

SELECT COUNT(*) AS Expr1, min(a.Country) AS Expr2
FROM tblSurvey a INNER JOIN
	tblSurvey b ON SOUNDEX(a.Country) = SOUNDEX(b.Country)
GROUP BY soundex(a.Country)
ORDER BY min(a.Country)

 

[KamranFeroz]

Thnx Von for the reply,

It seems that my result is not correct. The result getting through that query is e.g. Bahrain=841 instead of 29. The actual value is multiplied with the same and appears as result.

Any solution will be highly appreciable.

Web Programmer.

http://www.kamranferoz.com

 

[KamranFeroz]

I've got the solution

Query will be like that,

SELECT COUNT(*) AS Expr1, MIN(Country) AS Expr2
FROM tblSurvey
GROUP BY SOUNDEX(Country)
ORDER BY MIN(Country)

Thnx any way Von!

Cheers!