SQL: Count Distinct and Combine Similarly-Named

Question

Language: T-SQL
Server: SQL Server 2014

I have a table of PCs and would like to get a count of each PC by the manufacturer. That is simple, but when pulling the data from each PC some older HP computers report their manufacturer as "Hewlett-Packard" while newer machines report their manufacturer as "HP". I'd like to combine these into "HP". The query that doesn't do any replacement/combining is as follows:

SELECT 
    DISTINCT f_assetmanufacturer AS 'Manufacturer',
    COUNT(DISTINCT f_assettagID) AS 'Number of Each'
FROM tb_assets
GROUP BY f_assetmanufacturer

Sample output based on current query:

Manufacturer      Number of Each
Dell Inc.         10
Hewlett-Packard   6
HP                6

Desired Output:

Manufacturer      Number of Each
Dell Inc.         10
HP                12

How can the query be rewritten to combine "HP" and "Hewlett-Packard" COUNTs?

Answer 1

Use REPLACE on the manufacturer column to get the formatted string you need.

SELECT COUNT(*), REPLACE(Mfg, 'Hewlett-Packard', 'HP')
FROM dbo.SO41271540
GROUP BY REPLACE(Mfg, 'Hewlett-Packard', 'HP')