Group by LIKE of a column value

Question

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)?

Tag your question with the database you are using. – Gordon Linoff Mar 20 '17 at 18:28 — Gordon Linoff, Mar 20 '17 at 18:28
Post what would be the desired result – Lamak Mar 20 '17 at 18:28 — Lamak, Mar 20 '17 at 18:28
@GordonLinoff updated, thanks for the tip. – boisterouslobster Mar 20 '17 at 18:35 — boisterouslobster, Mar 20 '17 at 18:35
@lamak Updated at bottom – boisterouslobster Mar 20 '17 at 18:35 — boisterouslobster, Mar 20 '17 at 18:35
Then use @GordonLinoff's answer, it does exactly this – Lamak Mar 20 '17 at 18:37 — Lamak, Mar 20 '17 at 18:37

score 27 · Accepted Answer · answered Mar 20 '17 at 18:29

27

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.

answered Mar 20 '17 at 18:29

Gordon Linoff

1,242,037
58
646
786

1

Exactly what the OP was looking for, +1 – SqlZim Mar 20 '17 at 18:31
Hmm question -- why is it necessary to use LIKE statements in the SELECT part of the query? Would the LIKE statements in the GROUP BY not be sufficient? – boisterouslobster Mar 20 '17 at 18:40
1

@Gordon Linoff Good answer +1... Answer would be even better if you include the `ORDER BY COUNT(*) DESC, plan_type ASC` then the query should give the desired result: – Raymond Nijland Mar 20 '17 at 18:47

score 3 · Answer 2 · answered Mar 20 '17 at 18:29

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)

score 1 · Answer 3 · answered Mar 20 '17 at 18:43

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)

score 0 · Answer 4 · edited Aug 23 '19 at 18:23

0

Try the following:

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

edited Aug 23 '19 at 18:23

Arkistarvh Kltzuonstev

6,824
7
26
56

answered Jan 24 '19 at 21:56

FrankRex

1

Group by LIKE of a column value

4 Answers4

Linked