boisterouslobster
boisterouslobster

Reputation: 1293

Group by LIKE of a column value

Given a hypothetical query such as this one:

SELECT COUNT(*)
FROM subscriptions
GROUP by plan_type

And a table similar to the one below:

+----+-----------------------+-------------+--+
| id |       plan_type       | customer_id |  |
+----+-----------------------+-------------+--+
|  1 | gold_2017             |         523 |  |
|  2 | gold_2016_recurring   |        2300 |  |
|  3 | silver_2016           |         234 |  |
|  4 | silver_2017_recurring |        2593 |  |
|  5 | platinum_recurring    |        4123 |  |
+----+-----------------------+-------------+--+

Desired result:

+-------+----------+
| count |   type   |
+-------+----------+
|     2 | gold     |
|     2 | silver   |
|     1 | platinum |
+-------+----------+

Is there any way to group these entries using a GROUP BY and a LIKE statement (LIKE "silver", LIKE "gold", LIKE "platinum", etc)?

Upvotes: 13

Views: 15692

Answers (4)

FrankRex
FrankRex

Reputation: 1

Try the following:

SELECT SUBSTRING_INDEX(plan_type, '_', 1) FROM subscriptions
Group By SUBSTRING_INDEX(Remotehost, '_', 1)

Upvotes: 0

Rajendra
Rajendra

Reputation: 191

Adding like clause should work as below query :

SELECT COUNT(*) AS count,
(CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
             WHEN plan_type LIKE 'gold%' THEN 'gold'
             WHEN plan_type LIKE 'platinum%' THEN 'platinum'
        END) AS type
FROM subscriptions
GROUP by (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
           WHEN plan_type LIKE 'gold%' THEN 'gold'
           WHEN plan_type LIKE 'platinum%' THEN 'platinum' END)

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

You can group on some string function to reduce your plan type to the substring you want.

Sql Server example:

SELECT 
    left(plan_type,charindex('_',plan_type)-1) as plan_type
  , COUNT(*)
FROM subscriptions
GROUP by left(plan_type,charindex('_',plan_type)-1) 

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use case:

SELECT (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
             WHEN plan_type LIKE 'gold%' THEN 'gold'
             WHEN plan_type LIKE 'platinum%' THEN 'platinum'
        END) as plan_grp, COUNT(*)
FROM subscriptions
GROUP by (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
               WHEN plan_type LIKE 'gold%' THEN 'gold'
               WHEN plan_type LIKE 'platinum%' THEN 'platinum'
          END);

Some databases allow you to use a column alias in the GROUP BY.

Upvotes: 27

Related Questions