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

find the percentage share of each language from the a month of 30 days: getting errors – Mysql

I used this MySQL table.

I am trying to find the percentage share of each language over the course of 30 days from it.

select language as languages, round(100*count(*)/total, 2)
from job_data
cross join (select count(*) as total from job_data) sub
group by language;

The error is highlighted in the ‘Output’ tab.

WITH CTE AS (
    SELECT Language, COUNT(job_id) AS num_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
    AND ds >= "2020-11-01" AND  ds <= "2020-11-30"
    GROUP BY language),
total AS (
    SELECT COUNT(job_id) AS total_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
    AND ds >= "2020-11-01" AND  ds <= "2020-11-30"
    GROUP BY language)
SELECT language, ROUND(100.0*num_jobs/total_jobs, 2) AS perc_jobs
FROM CTE
ORDER BY perc_job DESC;

Error Code: 1054. Unknown column 'total_jobs' in 'field list'

I understood the error I got while I tried the second method.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    select language, count(distinct language),
    round(100*count(*)/(select count(*) from job_data),2) as percentage_share
from job_data
group by language order by language desc;

  2. 0

    I think you want window functions:

    select language, count(*) cnt, count(*) / sum(count(*)) over() ratio
from job_data 
where event in ('transfer', 'decision') 
    and ds >= '2020-11-01' 
    and ds <  '2020-12-01'
group by language

    This aggregates your table by language, then computes the counts; expression sum(count(*)) over() computes the total of all counts – you can use it as a denumerator of the division.

    Notes:

    • window functions are available in MySQL 8.0 only
    • use single quotes rather than double quotes for literal strings (and dates); this followos the SQL standard, which all databases support
    • I would recommend filtering dates with half-open intervals rather than between (that typically works better if your dates have a time portion)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search