问题

以下是翻译好的部分：

我有一个查询，想知道每月有6个或更多活动的用户有多少，但我只有关系，没有每月的计数。

这是我的查询：

SELECT 
MONTH(12_main.timestamp) AS 'MONTH',

(SELECT 12_main.user) AS 'USER'

FROM 12_main

INNER JOIN 12_pack ON 12_main.pack = 12_pack.pack_id
INNER JOIN 12_ubication_pack ON 12_pack.pack_id = 12_ubication_pack.pack
INNER JOIN 10_working_ubication ON 12_ubication_pack.ubication = 10_working_ubication.id
INNER JOIN 12_productive_areas ON 10_working_ubication.working_line = 12_productive_areas.working_line

WHERE 12_main.status_cert = '4'
AND YEAR(12_main.timestamp) = '2023'

GROUP BY 12_main.user
HAVING COUNT(*) >= 6;

这个查询给我以下结果：

我想要的是月份和用户数量：

<details>
<summary>英文:</summary>

I have a query to know how many users have  6 or more activities per month but I only have the relation not the count per month

this is my query 

SELECT
MONTH(12_main.timestamp) AS 'MONTH' ,

(SELECT 12_main.user )AS 'USER'

FROM 12_main

INNER JOIN 12_pack on 12_main.pack =12_pack.pack_id
INNER JOIN 12_ubication_pack on 12_pack.pack_id= 12_ubication_pack.pack
INNER JOIN 10_working_ubication on 12_ubication_pack.ubication =10_working_ubication.id
INNER JOIN 12_productive_areas on 10_working_ubication.working_line=12_productive_areas.working_line

WHERE 12_main.status_cert ='4'
AND YEAR(12_main.timestamp)='2023'

GROUP BY 12_main.user
HAVING count( * )>=6
;

that give me this 


| MONTH |   USER   |
| ----- | -------- |
| 1     | JUAN12   |
| 1     | MARY23   |
| 2     | JOS123   |
| 2     | DAN454   |
| 3     | ROB345   |


AND I WANT 
the months and the quantity of users 

| MONTH |   USER   |
| ----- | -------- |
| 1     |      2   |
| 2     |      2   |
| 3     |      1   |




</details>


# 答案1
**得分**: 0

尝试使用按月分组，使代码如下，将解决此问题：

```sql
SELECT 
MONTH(12_main.timestamp) AS 'MONTH', 
COUNT(DISTINCT 12_main.user) AS 'USER_COUNT' 
FROM 12_main 

INNER JOIN 12_pack ON 12_main.pack = 12_pack.pack_id 
INNER JOIN 12_ubication_pack ON 12_pack.pack_id = 12_ubication_pack.pack 
INNER JOIN 10_working_ubication ON 12_ubication_pack.ubication = 
10_working_ubication.id 
INNER JOIN 12_productive_areas ON 10_working_ubication.working_line = 
12_productive_areas.working_line 

WHERE 12_main.status_cert = '4' 
AND YEAR(12_main.timestamp) = '2023' 

GROUP BY MONTH(12_main.timestamp) HAVING COUNT(DISTINCT 12_main.user) >= 6;

SELECT 
MONTH(12_main.timestamp) AS 'MONTH' , 
COUNT(DISTINCT 12_main.user) AS 'USER_COUNT' 
FROM 12_main 

INNER JOIN 12_pack ON 12_main.pack = 12_pack.pack_id 
INNER JOIN 12_ubication_pack ON 12_pack.pack_id = 12_ubication_pack.pack 
INNER JOIN 10_working_ubication ON 12_ubication_pack.ubication = 
10_working_ubication.id 
INNER JOIN 12_productive_areas ON 10_working_ubication.working_line = 
12_productive_areas.working_line 

WHERE 12_main.status_cert = '4' 
AND YEAR(12_main.timestamp) = '2023' 

GROUP BY MONTH(12_main.timestamp) HAVING COUNT(DISTINCT 12_main.user) >= 6;