How to return a count of a group_concat in sql?

Question

The query below returns all of different subscriptions a user has. In another column, I would like to return the count of how many times a given subscription occurs. Any advice on how do this?

I tried including

COUNT(GROUP_CONCAT(subscription_plan_id))

but it doesn't work.

SELECT GROUP_CONCAT(subscription_plan_id)
FROM
  subscriptions
WHERE 
  created_at BETWEEN '2014-01-01' AND '2014-01-31'
GROUP BY
  user_id
HAVING
  COUNT(subscription_plan_id) > 1

Desired output:

group_concat...count
1,2,3...2
2,3...5

Please show sample data and the desired result. I'm not really sure what you want. — Barmar
– Barmar, Commented May 2, 2014 at 17:08
The above just counts the number of subscriptions each user has. I want to a count of how many times a given subscription path occurs. How many times does a user have subscription_plan_id 1 and 2? — sharataka
– sharataka, Commented May 2, 2014 at 17:12
I still don't understand. How does 2,3 result in 5 as the count? Please show the input data and the desired output. Best would be to make a sqlfiddle. — Barmar
– Barmar, Commented May 2, 2014 at 17:14

Gordon Linoff · Accepted Answer · 2014-05-02 17:12:35Z

1

Just put the count() in the select:

SELECT GROUP_CONCAT(subscription_plan_id), COUNT(subscription_plan_id) as NumSubs
FROM subscriptions
WHERE created_at BETWEEN '2014-01-01' AND '2014-01-31'
GROUP BY user_id
HAVING COUNT(subscription_plan_id) > 1;

answered May 2, 2014 at 17:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lalit Patel · Accepted Answer · 2014-05-03 06:11:30Z

0

Add count And Group by subscription_plan_id

SELECT GROUP_CONCAT(subscription_plan_id), COUNT(subscription_plan_id) as NumSubs FROM subscriptions WHERE created_at BETWEEN '2014-01-01' AND '2014-01-31' GROUP BY subscription_plan_id HAVING COUNT(subscription_plan_id) > 1;

answered May 3, 2014 at 6:11

Lalit Patel

1081 silver badge9 bronze badges

Collectives™ on Stack Overflow

How to return a count of a group_concat in sql?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related