CODEWITHSUNDEEP
sqlsql-server-2008
chibis
chibis

Reputation: 858

combining records in counts

I have a table from which I need to get counts grouped by field like this:

SELECT COUNT(*), fld1
FROM tbl1
GROUP BY fld1

This is simple, however fld1 may contain values like "Exp Brain Res", "Exp. Brain Res.", "Exp. Brain Res.", "Exp.Brain Res.", etc. I need to combine all these possible values into one count and display with one of those names. So rather than having in result set

Exp Brain Res   - 5
Exp. Brain Res. - 3
Exp. Brain Res. - 2
Exp.Brain Res.  - 4

I need to have 

Exp Brain Res (or any of the above names) - 14.

What would be a good way to do this in SQL 2008?

Thank you

Upvotes: 1

Views: 31

Answers (1)

D Stanley
D Stanley

Reputation: 152626

If spaces and periods are the only punctuation you want to ignore you can use:

SELECT MAX(fld1), -- select one of the group's values.
       COUNT(*)
FROM tbl1 
GROUP BY REPLACE(REPLACE(fld1, ' ', '' ), '.', '')

Upvotes: 1

Related Questions