mwafi mwafi - 4 months ago 14
MySQL Question

MySQL: count with group by and max limit

I need to count taggers efforts, by count how many tags added by each tagger, with maximum tag for each article = 5 tags.

ex:


  1. If tagger added 4 tags to one Article count 4.

  2. If tagger added 5 tags to one Article count 5.

  3. If tagger added 6 tags to one Article count 5.

  4. If tagger added 10 tags to one Article count 5.



table structure
article_tags


id user_id article_id tag_id
1 1 1 1
2 1 1 2
3 1 2 1
4 2 3 1
5 2 3 2


Is there advanced use of Group by with limit, or something like this?
thx,

update:

I need to return how many tags added to articles, with max 5 for a single article, (so If someone added 30 tags for 30 articles = 30

Answer

Is this what you want?

select user_id, least(count(*), 5)
from article_tags
group by user_id;

EDIT:

I think you want two levels of aggregation:

select article_id, sum(cnt) as restricted_tag_count
from (select user_id, article_id, least(count(*), 5) as cnt
      from article_tags
      group by user_id, article_id
     ) ua
group by article_id;