SQL Group by "Like"

Question

I have a query where I generate our monthly customer contact activity. We have several categories (email out, email in, phone call in, phone call out, etc.) There are 8 distinct "type" results. I need to have two groups-one for all "email" and one for all "phone". Currently, I have a WHERE TYPE LIKE '%Email%'and TYPE LIKE '%Call%'. However, I am not able to group by these two "LIKE" statements. Does anyone know how I can best achieve this?

I simplified the query down to this for the example:

SELECT     TYPE
FROM         dbo.HISTORY
WHERE     (TYPE LIKE '%email%') OR
                      (TYPE LIKE '%call%')

Answer 1

This should work:

SELECT
    TYPE
FROM
    dbo.HISTORY
WHERE
    (TYPE LIKE '%email%') OR (TYPE LIKE '%call%')
GROUP BY
    CASE
        WHEN type LIKE '%email%' THEN 'email'
        WHEN type LIKE '%call%' THEN 'call'
        ELSE NULL
    END

Although, my advice would be to have a type code table with another column that tells whether each type is considered an email or call. Then you're not reliant on the type name following a specific format which is sure to be forgotten down the road. You can then easily group on that:

SELECT
    H.type
FROM
    dbo.History
INNER JOIN dbo.History_Types HT ON
    HT.history_type_code = H.history_type_code AND
    HT.history_type_category IN ('Email', 'Call')
GROUP BY
    HT.history_type_category

Answer 2

SELECT H.type FROM dbo.History INNER JOIN dbo.History_Types HT ON HT.history_type_code = H.history_type_code AND HT.history_type_category IN ('Email', 'Call') GROUP BY HT.history_type_category

Wouldn't it be better to put the filter statement into the where clause?

SELECT
    H.type FROM
    dbo.History 
    INNER JOIN dbo.History_Types HT ON
    HT.history_type_code = H.history_type_code 
WHERE **HT.history_type_category IN ('Email', 'Call')**
    GROUP BY   HT.history_type_category