I need to get the percentage of an alias column in my MYSQL query.

The query I have is as follows..

SELECT
    datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
    COUNT(*) as total_jobs
FROM
    crm_customer
RIGHT JOIN crm_job
    ON crm_customer.customer_id = crm_job.customer_id 
LEFT JOIN crm_customer_account
    ON crm_customer.customeraccount_id = crm_customer_account.customer_account_id
where account_description='EWC' and job_completed_date IS NOT NULL
GROUP BY days_diff

I need to add a further column called percentage_jobs to the right of the total_jobs column that shows the percentage of the total_jobs. By this I mean the percentage_jobs for the top row would be 20% the second row would be 60% and the last row would also be 20%.

Thanks in advance.

Regards

Alan

CodePudding user response：

If your version of MySql is 8.0 you can use SUM() window function for the new column:

SELECT datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
      COUNT(*) AS total_jobs,
      100 * COUNT(*) / SUM(COUNT(*)) OVER () AS percentage_jobs
FROM ...

CodePudding user response：

Put your query into a CTE. Then you can get the sum of total_jobs and divide by it to get the percentage.

WITH cte AS (
    SELECT
        datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
        COUNT(*) as total_jobs
    FROM
        crm_customer
    RIGHT JOIN crm_job
        ON crm_customer.customer_id = crm_job.customer_id 
    LEFT JOIN crm_customer_account
        ON crm_customer.customeraccount_id = crm_customer_account.customer_account_id
    where account_description='EWC' and job_completed_date IS NOT NULL
    GROUP BY days_diff
)
SELECT cte.*, ROUND(100 * total_jobs / total_total_jobs) AS percentage_jobs
FROM cte
CROSS JOIN (
    SELECT SUM(cte.total_jobs) AS total_total_jobs
    FROM cte
) AS t

CTE requires MySQL 8.x. If you have to do this in an older version, define the subquery as a view.

CREATE OR REPLACE VIEW total_jobs AS
    SELECT
        datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
        COUNT(*) as total_jobs
    FROM
        crm_customer
    RIGHT JOIN crm_job
        ON crm_customer.customer_id = crm_job.customer_id 
    LEFT JOIN crm_customer_account
        ON crm_customer.customeraccount_id = crm_customer_account.customer_account_id
    where account_description='EWC' and job_completed_date IS NOT NULL
    GROUP BY days_diff;

SELECT tj.*, ROUND(100 * tj.total_jobs / ttj.total_total_jobs) AS percentage_jobs
FROM total_jobs AS tj
CROSS JOIN (
    SELECT SUM(total_jobs) AS total_total_jobs
    FROM total_jobs
) AS ttj