Average calculation ignoring some data-CodePudding

I want to calculate the average from these data. I don't want to include in this average users who has done less or equal to 3 orders.

I use this code to calculate the total number of orders for each user:

SELECT DISTINCT `User`, COUNT(`User`) FROM `DB` GROUP BY`User`;

I get the result: User1 = 9, User2 = 3, User3 = 8. How can I calculate the average of this data so that the user is not taken into account and the average would be 8.5

date                | User  | Type
-----------------------------------
17-01-2022 00:00:00 | User1 | A
17-01-2022 00:00:00 | User1 | A
17-01-2022 00:00:00 | User1 | A
17-01-2022 00:00:00 | User2 | A
18-01-2022 00:00:00 | User3 | B
18-01-2022 00:00:00 | User3 | B
18-01-2022 00:00:00 | User3 | B
19-01-2022 00:00:00 | User1 | B
19-01-2022 00:00:00 | User2 | B
19-01-2022 00:00:00 | User2 | C
19-01-2022 00:00:00 | User3 | C
20-01-2022 00:00:00 | User3 | C
20-01-2022 00:00:00 | User3 | C
20-01-2022 00:00:00 | User3 | C
20-01-2022 00:00:00 | User1 | C
21-01-2022 00:00:00 | User1 | A
21-01-2022 00:00:00 | User1 | A
21-01-2022 00:00:00 | User3 | A
21-01-2022 00:00:00 | User1 | A
21-01-2022 00:00:00 | User1 | A

CodePudding user response：

Use a HAVING clause to exclude users with less or equal than 3 orders and then calculate the average:

SELECT AVG(counter) AS avg_counter
FROM (
  SELECT `User`, COUNT(`User`) AS counter
  FROM `DB` 
  GROUP BY`User`
  HAVING counter > 3
) t;