SQL Group By

Question

If I have a set of records

name    amount    Code
Dave    2         1234
Dave    3         1234
Daves   4         1234

I want this to group based on Code & Name, but the last Row has a typo in the name, so this wont group.

What would be the best way to group these as:

Dave/Daves 9 1234

Answer 1

Fix the typo? Otherwise grouping on the name is going to create a new group.

Fixing your data should be your highest priority instead of trying to devise ways to "work around" it.

It should also be noted that if you have this single typo in your data, it is likely that you have (or will have at some point in the future) even more screwy data that will not cleanly fit into your code, which will force you to invent more and more "work arounds" to deal with it, when you should be focusing on the cleanliness of your data.

Answer 2

If it's a workaround, try

SELECT cname, SUM(amount)
FROM (
  SELECT CASE WHEN NAME = 'Daves' THEN 'Dave' ELSE name END AS cname, amount
  FROM mytable
)
GROUP BY cname

This if course will handle only this exact case.

Answer 3

As a general rule if the data is wrong you should fix the data.

However if you want to do the report anyway you could come up with another criteria to group on, for example LEFT(Name, 4) would perform a grouping on the first 4 characters of the name.

You may also want to consider the CASE statement as a method (CASE WHEN name = 'Daves' THEN 'Dave' ELSE name), but I really don't like this method, especially if you are proposing to use this for anything else then a one-off report.

Answer 4

GROUP BY SanitizeByMagicallyDeterminingIfAStringIsATypo(name)

Your version of sql may not have that function.

Answer 5

For MySQL:

select
  group_concat(distinct name separator '/'),
  sum(amount),
  code
from
  T
group by
  code

For MSSQL 2005+ group_concat() can be implemented as .NET custom aggregate.

Answer 6

If the name field is suppose to be a key then the assumption has to be that Dave and Daves are two different items all together, and thus should be grouped differently. If however it is a typo, then as other have suggested, fix the data.

Grouping on a freeform entered text field if that is what this is, will always have issues. Data entry is never 100%.

To me it makes more sense to group on the code alone if that is the key field and leave name out of the grouping all together.