How to get count(*) return 0

Question

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 name and risk category for the last 3 months.

I have this sql request :

SELECT MONTH(risk.creation_date) as month, count(*) as number, risk_category.name as risk_name FROM risk As risk
 JOIN risk_subcategory on risk_subcategory.id = risk.risk_subcategory_id
  JOIN risk_category on risk_category.id = risk_subcategory.risk_category_id
where risk.creation_date >= (NOW()-INTERVAL 3 MONTH) GROUP BY MONTH(risk.creation_date), risk_category.name;

it return this result set :

      month number  risk_name
---------------------------
      12      1      Risk 1
      12      3      Risk 2
       1      1      Risk 3
       1      9      Risk 2
       2      1      Risk 3
       2      1      Risk 1
       2     10      Risk 2

I want this result (including 0) :

  month number  risk_name
---------------------------
    12   1       Risk 1
    12   3       Risk 2
    12   0       Risk 3
    1    0       Risk 1
    1    1       Risk 3
    1    9       Risk 2
    2    1       Risk 3
    2    1       Risk 1
    2    10      Risk 2

How can I do ? Thanks

I think you need to use Inner Join to get left and right combination — Martin Ocando, Mar 15 '17 at 15:17
How do you get this result `1 0 Risk 1`? what is the logic? consider including sample data — Stephen, Mar 15 '17 at 15:24
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 name and risk category — MK-rou, Mar 15 '17 at 15:32

score 3 · Answer 1 · answered Mar 15 '17 at 15:19

3

You are selecting the month from the same table on which you perform the count, so if the count for month x is supposed to be 0 it means that there are no rows with month = x. You cannot get that month from that table, since it's simply not there.

If you want to get all the months, you should use another table where you have all the months available, and perform a left join between that table and risk, like

select  t1.month, coalesce(t2.count(*), 0)), t2.risk_name
from    months t1
left join
        risk t2
on      t1.month = MONTH(risk.creation_date)
group by t1.month, t2.risk_name

answered Mar 15 '17 at 15:19

Stefano Zanini

5,876
2
13
33

please read my question before send answer, month is extracted from creation_date field of risk table – MK-rou Mar 15 '17 at 15:24
No need to be rude, please read my answer more carefully and if it's not clear enough just ask for further explaination. I read the question and my answer is valid, you cannot make a query display months that don't exists on your tables. – Stefano Zanini Mar 15 '17 at 15:28
I have provide my sql request, if there is something wrong with it please tell it or provide me another sql request with my given tables, I can't accept a generic answer who deserve to be in comment and not in answers – MK-rou Mar 15 '17 at 15:42
coalesce work when count return NULL , in my result there is no NULL value – MK-rou Mar 15 '17 at 15:46
Coalesce would work in the alternative scenario I suggested you (a table that serves as months lookup), where you would have `NULL` values. The point is that with the tables you have, you just cannot achieve what you desire. – Stefano Zanini Mar 15 '17 at 15:50
2

My dear lord. Has that type of rudeness towards someone who is trying to help you been fruitful in your past? Your previous questions here seem similar. Bless your heart for your conviction, but it doesn't make me want to use the extended time needed to write an answer detailed enough to communicate a solution to you, even if I might know.. – thebjorn Mar 15 '17 at 18:09
Answering question just for vote and with wrong answer is not the goal of stackoverflow – MK-rou Mar 15 '17 at 20:11

MK-rou · Accepted Answer · 2017-03-15T16:23:17.227

0

According to voted answer, I have try this but it doesn't work :

   SELECT MONTH(risk.creation_date) as month, count(*) as number, risk_category.name as risk_name FROM risk As risk
  JOIN risk_subcategory on risk_subcategory.id = risk.risk_subcategory_id
   JOIN risk_category on risk_category.id = risk_subcategory.risk_category_id
 LEFT JOIN (
    SELECT mois
    FROM
      (
        SELECT 1 AS mois
        UNION SELECT 2 AS mois
        UNION SELECT 3 AS mois
        UNION SELECT 4 AS mois
        UNION SELECT 5 AS mois
        UNION SELECT 6 AS mois
        UNION SELECT 7 AS mois
        UNION SELECT 8 AS mois
        UNION SELECT 9 AS mois
        UNION SELECT 10 AS mois
        UNION SELECT 11 AS mois
        UNION SELECT 12 AS mois
      ) as year
    ) as months on months.mois = MONTH(risk.creation_date)
where risk.creation_date >= (NOW()-INTERVAL 3 MONTH) GROUP BY MONTH(risk.creation_date), risk_category.name;

edited Mar 15 '17 at 16:23

answered Mar 15 '17 at 16:07

MK-rou

686
2
9
30

2

Change the 'LEFT JOIN' into a 'RIGHT JOIN' to make it work. Now you start from { risk, risk_subcategory, risk_category } and LEFT JOIN it with your derived-table 'year'. You can also start with your derived-table 'year' and then LEFT JOIN against { risk, risk_subcategory, risk_category }. – eremmel Mar 15 '17 at 16:36
the result is the same – MK-rou Mar 15 '17 at 16:48
You need to replace 'MONTH(risk.creation_date)' by 'months.mois' in SELECT and GROUP BY as well, then you are using the months table as basis for your query (excuse of being incomplete). – eremmel Mar 16 '17 at 17:39

How to get count(*) return 0

2 Answers2