Displayed values are not what they should be-CodePudding

There are 2 tables ost_ticket and ost_ticket_action_history.

  create table ost_ticket(
ticket_id int not null PRIMARY KEY,
created timestamp,
staff bool,
status varchar(50),
city_id int
);
create table ost_ticket_action_history(
    ticket_id int not null,
    action_id int not null PRIMARY KEY,
    action_name varchar(50),
    started timestamp,
    FOREIGN KEY(ticket_id) REFERENCES ost_ticket(ticket_id)
);

In the ost_ticket_action_history table the data is:

  INSERT INTO newdb.ost_ticket_action_history (ticket_id, action_id, action_name, started) VALUES (1, 1, 'Consultation', '2022-01-06 18:30:29');
INSERT INTO newdb.ost_ticket_action_history (ticket_id, action_id, action_name, started) VALUES (2, 2, 'Bank Application', '2022-02-06 18:30:45');
INSERT INTO newdb.ost_ticket_action_history (ticket_id, action_id, action_name, started) VALUES (3, 3, 'Consultation', '2022-05-06 18:42:48');

In the ost_ticket table the data is:

INSERT INTO newdb.ost_ticket (ticket_id, created, staff, status, city_id) VALUES (1, '2022-04-04 18:26:41', 1, 'open', 2);
INSERT INTO newdb.ost_ticket (ticket_id, created, staff, status, city_id) VALUES (2, '2022-05-05 18:30:48', 0, 'open', 3);
INSERT INTO newdb.ost_ticket (ticket_id, created, staff, status, city_id) VALUES (3, '2022-04-06 18:42:53', 1, 'open', 4);

My task is to get the conversion from the “Consultation” stage to the “Bank Application” stage broken down by months (based on the start date of the “Bank Application” stage).Conversion is calculated according to the following formula: (number of applications with the “Bank Application” stage / number of applications with the “Consultation” stage) * 100%.

My request is like this:

select SUM(action_name='Bank Application')/SUM(action_name='Consultation') * 2 as 'Conversion' from ost_ticket_action_history JOIN ost_ticket ot on ot.ticket_id = ost_ticket_action_history.ticket_id where status = 'open' and created > '2020 -01-01 00:00:00' group by action_name,started having action_name = 'Bank Application';

As a result I get:

Another query:

SELECT 
    SUM(CASE
        WHEN b.ticket_id IS NOT NULL THEN 1
        ELSE 0
    END) / COUNT(*) conversion,
    YEAR(a.started) AS 'year',
    MONTH(a.started) AS 'month'
FROM
    ost_ticket_action_history a
        LEFT JOIN
    ost_ticket_action_history b ON a.ticket_id = b.ticket_id
        AND b.action_name = 'Bank Application'
WHERE
    a.action_name = 'Consultation'
        AND a.status = 'open'
        AND a.created > '2020-01-01 00:00:00'
GROUP BY YEAR(a.started) , MONTH(a.started)

I apologize if I didn't write very clearly. Please explain what to do.

CodePudding user response：

Like i expained in my comment, you exclude rows with your HAving clause

I will show you in the next how t debug

#First chekc what the raw result of the select qury is

As you see , when you remove the GROUP BY and see what you actually get

is only 1 row with Bank Application, because the having clause excludes all other rows

SELECT 
* 
FROM
    ost_ticket_action_history
        JOIN
    ost_ticket ot ON ot.ticket_id = ost_ticket_action_history.ticket_id
WHERE
    status = 'open'
        AND created > '2020-01-01 00:00:00'
#GROUP BY action_name , started
HAVING action_name = 'Bank Application';

ticket_id | action_id | action_name      | started             | ticket_id | created             | staff | status | city_id
--------: | --------: | :--------------- | :------------------ | --------: | :------------------ | ----: | :----- | ------:
        2 |         2 | Bank Application | 2022-02-06 18:30:45 |         2 | 2022-05-05 18:30:48 |     0 | open   |       3

#second step, see what the result set is without calculating anything.

As you can see you make a division with 0, what you have learned in school, is forbidden, hat is why you have as result set NULL

SELECT 
 
 SUM(action_name = 'Bank Application') 
 #/
 ,SUM(action_name = 'Consultation') * 2 AS 'Conversion'
FROM
    ost_ticket_action_history
        JOIN
    ost_ticket ot ON ot.ticket_id = ost_ticket_action_history.ticket_id
WHERE
    status = 'open'
        AND created > '2020-01-01 00:00:00'
GROUP BY action_name , started
HAVING action_name = 'Bank Application';

SUM(action_name = 'Bank Application') | Conversion
------------------------------------: | ---------:
                                    1 |          0

db<>fiddle here

#Third what you can do exclude a division with 0, here i didn't remove all othe rows as this is only for emphasis

SELECT 
 
 SUM(action_name = 'Bank Application') 
 /
 SUM(action_name = 'Consultation') * 2 AS 'Conversion'
FROM
    ost_ticket_action_history
        JOIN
    ost_ticket ot ON ot.ticket_id = ost_ticket_action_history.ticket_id
WHERE
    status = 'open'
        AND created > '2020-01-01 00:00:00'
GROUP BY action_name , started
HAVING SUM(action_name = 'Consultation') > 0;

| Conversion |
| ---------: |
|     0.0000 |
|     0.0000 |

db<>fiddle here

Final words, If you get a strange result, simply go back remove everything that doesn't matter and try to get all values, so hat you can check your math