Pfthb

I'm a bit stuck cause am not sure if I am looking the wrong way casue I can't find anything related to what I am looking for. So I want to give an example for what I am trying to do. NOTE: not real scenario.

I have a count on a field:

SELECT count(user_id) 
FROM usersgroups 
group by user_id

Now I want to make a calc with the max value from the count. Something like:

SELECT count(user_id) as count, 
 SUM(max(count(user_id)) / count(user_id)) as blub 
FROM usergroups 
group by user_id

But I get error invalid use of group function cause I think I cant use the COUNT() function inside the SUM function?

So is there any other way to make that calculation.

P.S. the calc is for calculating the percentage of the max value to the record/current value.

UPDATE

So what I expect is a percentage like

 count | percentage 
5 | 1
4 | 0.8
3 | 0.6
2 | 0.3
1 | 0.2

• 5/5 = 1


• 4/5 = 0.8


• 3/5 = 0.6


• 2/5 = 0.4


• 1/5 = 0.2

• In a separate Derived Table, you can fetch the maximum count value out of all the counts. I have simply used ORDER BY COUNT(user_id) DESC LIMIT 1 to fetch the same.


• 
  CROSS JOIN this result-set with the usergroups table, so that every row in the usergroups table has access to the maximum count value.


• Now, you can simply use the GROUP BY and appropriate aggregation to determine the "percent".


• Note that, for GROUP BY to be valid, SELECT clause must contain either aggregated columns/expressions only, or the columns specified in the GROUP BY clause. That is why MAX() is used over the maximum count value. Since that value is only a scalar, so MAX() will return the same value only.

Try the following:

SELECT
 ug.user_id, 
 COUNT(ug.user_id) AS user_count, 
 COUNT(ug.user_id) / MAX(mcnt.count) AS percent
FROM 
 usergroups AS ug 
CROSS JOIN 
(
 SELECT COUNT(user_id) AS count
 FROM usersgroups 
 GROUP BY user_id
 ORDER BY COUNT(user_id) DESC LIMIT 1
) AS mcnt
GROUP BY ug.user_id 
ORDER BY user_count DESC 

Count available user_id as subquery, then divide user_id values by the sum of count.

 select user_id/ sum(count) as percentage 
 from(
 select count(user_id) as count , user_id
 from usergroups
 where user_id != 0
 group by user_id
 ) as A
 group by count, user_id