Question 20752 in Mysql Question posted in
The official MySQL documentation can be found here.

Aliases in MySQL and do maths

I need to workout the effectiveness of a user to display in a report, i complied the following statement

SELECT COUNT(`call_id`) AS logged 
            FROM `tbl_calls`
            WHERE `user_id_attended_by` = 24
            AND YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
            AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
            AND `fk_supplier_id` = 3
    UNION ALL 
        SELECT COUNT(call_id) AS ACK
            FROM `tbl_calls`
            WHERE YEAR(`date_logged`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
            AND MONTH(`date_logged`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
            AND `repaired_by` = 24
            AND `fk_supplier_id` = 3

The output for the above motioned statement as follows,

enter image description here

I need to get the percentages of the two totals in the pictures to add it to a report to get the users performance, I tried to create a temp table and entering the data into it but could not get it working in xampp.

Tags:

2

Answers


  1. 0 
    SELECT logged,
       100*logged/(logged+ack) percent_logged,
       ack,
       100*ack/(logged+ack) percent_ack
FROM (
    SELECT SUM(`user_id_attended_by` = 24) AS logged,
           SUM(repaired_by` = 24) AS ack
    FROM `tbl_calls`
    WHERE YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
      AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
      AND `fk_supplier_id` = 3
    ) subquery

    ?

    Login or Signup to reply.
  2. 0

    I have no idea what the difference is between date_ack_by_tech and date_logged, but I assume you have a reason for using them. So, I have not tried to smudge your queries together. If there is no need to differentiate between these two dates, then Akina’s method is preferable.

    I would drop the use of MONTH() and YEAR() and instead use a date range. This will make these criteria sargable, which may or may not be helpful in this case. But it also does away with your current January issue (Jan 2023 returns Dec 2022, whereas Feb 2023 returns Jan 2022 – 1 month or 13 months).

    SELECT (
    SELECT COUNT(`call_id`) AS logged 
    FROM `tbl_calls`
    WHERE `date_ack_by_tech` BETWEEN CURRENT_DATE - INTERVAL 13 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY
                                 AND CURRENT_DATE - INTERVAL 12 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL 1 SECOND
    AND `user_id_attended_by` = 24
    AND `fk_supplier_id` = 3
) AS logged, (
    SELECT COUNT(call_id) AS ack
    FROM `tbl_calls`
    WHERE `date_logged` BETWEEN CURRENT_DATE - INTERVAL 13 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY
                            AND CURRENT_DATE - INTERVAL 12 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL 1 SECOND
    AND `repaired_by` = 24
    AND `fk_supplier_id` = 3
) AS ack,
(SELECT ROUND(100 * logged / (logged + ack), 2)) AS percent_logged,
(SELECT ROUND(100 * ack / (logged + ack), 2)) AS percent_ack;

    If you want to continue using your MONTH() and YEAR() method, you should change the year criterion to:

    YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 13 MONTH)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search