how to count all records that are repeated more than 5 times in a date-CodePudding

I am trying to obtain in a single record grouped by user the total sum of the records that are repeated more than 5 times on a date

This is my query

select user_id, answer_date, count(*) as total_mayor_que_5
from items
WHERE items.proyecto_id = 6
  and `estado` IN (1,4)
  AND `liquidada` = 1
  and items.answer_date BETWEEN '2021-01-01' and '2021-12-31'
  and items.user_id = 832
GROUP BY user_id, answer_date
HAVING COUNT(answer_date) >= 5

and as result have this

user_id	answered_date	more_greater_than_5
832	2021-11-08	6
832	2021-11-09	6
833	2021-11-09	6
833	2021-11-09	5

I don't want it to show me all those records; instead, I want a total. For example, for the user with id 832, the total would be 12.

This is my structure table items

I want something like this

user_id	total
832	12
833	11

Can somebody help me? Thanks

CodePudding user response：

You want one more level of grouping

select user_id, sum(total_mayor_que_5) total
from (
    select user_id, answer_date, count(*) as total_mayor_que_5
    from items
    WHERE items.proyecto_id = 6
      and `estado` IN (1,4)
      AND `liquidada` = 1
      and items.answer_date BETWEEN '2021-01-01' and '2021-12-31'
      and items.user_id = 832
    GROUP BY user_id, answer_date
    HAVING COUNT(answer_date) >= 5
) t
GROUP BY user_id
order by user_id