Maximum occurance of number(id) in a table - Yii2

Question

I have a table with details regarding a user subscription(id, user_id, plan_id, product_id, amount..etc). I wanted to get favourite plan and product by getting maximum occurance of an id (plan_id, product_id) in a table column.

I was trying to use this Find most frequent value in SQL column. But didn't work out.

Here is my query for favorite plan:

$subs = Subscription::findBySql("SELECT  plan_id, COUNT(plan_id) AS `value_occurrence` FROM subscription GROUP BY plan_id
            ORDER BY `value_occurrence` DESC LIMIT 1");

I just need the maximum occurance of plan_id and product_id.

Thanks for any help.

*But didn't work out* - can you explain it more clearly ? you get an error or wrong result ? what is `var_dump($subs)` output ? — Tony, Jun 21 '15 at 06:53

score 0 · Answer 1 · answered Jun 21 '15 at 06:27

I think you need count of the occurrence of the plan_id and product_id in this way (I suppose your model is called Subscription):

$plantCounts = Subcription::find()
  ->select(['COUNT(*) AS value_occurrence'])
  ->orderBy(['value_occurrence'= DESC ]
  ->groupBy(['plan_id'])
  ->one();

score -1 · Answer 2 · answered Jun 21 '15 at 01:15

-1

From any row whatsoever even on different rows as a source, you want the max of plan_id and the max of product_id ? 1 row total coming back?

select max(plan_id) as plmax,
max(product_id) as prodmax
from subscription

answered Jun 21 '15 at 01:15

AsConfused

325
2
7

Just column names so u can use them like column names in your resultset of 1 row – AsConfused Jun 21 '15 at 01:28
Otherwise as aggregate functions it is harder to refer to them – AsConfused Jun 21 '15 at 01:30

Maximum occurance of number(id) in a table - Yii2

2 Answers2