group by SKU with count

Question

I have a table with parts in it:

parts (partID, sku, ....)

The SKU looks like:

ABC1232
ABC1332
DSE234
XYZ322
XYZ332
etc...

I need to group by manufacturer, so I have to get a substring of the SKU, taking the first 3 characters and then grouping them together and getting a count of them.

So the resulting output needs to look like:

MFG   COUNT
ABC   2343
DSE   43
XYX   323

Answer 1

SELECT SUBSTRING(sku, 1, 3) AS MFG, count(*) AS COUNT
    FROM parts
    GROUP BY SUBSTRING(sku, 1, 3)

Answer 2

You can also try

SELECT  LEFT(sku, 3) AS MFG, count(*) AS COUNT    
FROM parts    
GROUP BY LEFT(sku, 3)

Found at LEFT (Transact-SQL)