### Count（）返回total而不是count

I have qualified risks with description and creation date, who are attached to subcategory of risks this last ones are attached to category of risks, each risk has a name like 'Risk_1' , my aim is to count the number of risks by month and risk category including zero.

I have this request :

``````SELECT DISTINCT risk_names.type as risk_name, MONTH(risk.creation_date) as month, count(risk.id) as number FROM risk As risk , risk_category
JOIN  (
SELECT risk_category.name as type
FROM
risk_category
) as risk_names on risk_names.type = risk_category.name
where risk.creation_date >= (NOW()-INTERVAL 3 MONTH) GROUP BY MONTH(risk.creation_date), risk_names.type;
``````

Who return this result :

``````Risk_name          month  number
---------------------------------
Risk_1              1      10   ---> instead 8
Risk_2              1      10   ---> instead 1
Risk_3              1      10   ---> instead 1
Risk_1              2      12  ......
Risk_2              2      12
Risk_3              2      12
Risk_1              12     4
Risk_2              12     4
Risk_3              12     4
``````

As you can see the number returned is the total for each month , but my aim is to get total for each distinct risk. Can you help me . thanks

## 2 个解决方案

### #1

1

The comma in your `FROM` is doing a `CROSS JOIN`. A Cartesian product is unnecessary and throws all the counts off.

FROM中的逗号正在进行CROSS JOIN。笛卡尔积是不必要的，并将所有计数都抛弃。

I suspect you want something like this:

``````SELECT rc.type as risk_name, MONTH(r.creation_date) as month,
count(r.id) as number
FROM risk_category rc LEFT JOIN
risk r
ON r.?? = rc.??
where risk.creation_date >= (NOW()-INTERVAL 3 MONTH)
GROUP BY rc.type, MONTH(r.creation_date);
``````

I don't know what the `JOIN` criterion is between `risk` and `risk_category`.

### #2

0

Then try using `distinct` keuword with `count()` like `count(distinct risk.id) as number` instead
{"ft_lang_label":"__label__en","ft_lang_prob":0.52622855,"math_prob":0.9031177,"size":2006,"snap":"2019-51-2020-05","text_gpt3_token_len":756,"char_repetition_ratio":0.15284716,"word_repetition_ratio":0.037037037,"special_character_ratio":0.27268195,"punctuation_ratio":0.1519337,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9755027,"pos_list":[0,1,2],"im_url_duplicate_count":[null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-01-23T17:18:59Z\",\"WARC-Record-ID\":\"<urn:uuid:249d62ff-99e8-4782-ac31-b4bd7f627b27>\",\"Content-Length\":\"21346\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:f684e3e4-0232-4ff5-b0a6-d880e651edb3>\",\"WARC-Concurrent-To\":\"<urn:uuid:3c400cee-7f47-45ad-850b-56846cdd9822>\",\"WARC-IP-Address\":\"139.129.128.97\",\"WARC-Target-URI\":\"https://www.itdaan.com/blog/2017/03/16/7defea2357292a95a3530c5cebace75b.html\",\"WARC-Payload-Digest\":\"sha1:EC3KQMECU5DVP6FN2HDT6C2TBUTUUFJN\",\"WARC-Block-Digest\":\"sha1:SK7HOELVA6TLYSJ3NBZUTU53VUBE6QL6\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-05/CC-MAIN-2020-05_segments_1579250611127.53_warc_CC-MAIN-20200123160903-20200123185903-00511.warc.gz\"}"}