CODEWITHSUNDEEP
mysql
CHig1
CHig1

Reputation: 25

Adding a percentage Column to existing data in MYSQL

I have this as a answer since earlier today

SELECT  Users.full_name
        ,sum(total)
        ,sum(case when service < 49 then total else 0 end) AS Productive
FROM    time_report
        , users
WHERE   time_report.User = users.user_id
        AND date BETWEEN '2011-0502' AND '2011-05-11'
GROUP BY 
        User

THis gives me the following data back which is what I wanted

full_name   sum(total)  Productive

Cian Higgins    26  14

Wallace Ward    23  23

jason ward      42  33

Thomas Woods    72  53

Peter Jones     49  41

fintan corrigan 40  32

David Jones     35  27

January Jones   23  23

Joe Johnson     24  24

I am wondering it there a way to add an aditional column called percentage to the output which will give me the output from Round((Total/productive) * 100)

I have been struggling with this for most of the day. I have created a temp table with the output from the original query, but am struggling with the right syntax to get the percentage entered into it.

Any help appreciated.

Upvotes: 1

Views: 2364

Answers (3)

a1ex07
a1ex07

Reputation: 37384

You can avoid repeating aggregate expressions by moving your original query into FROM clause.

SELECT full_name, Total, Productive, Round((Total/Productive) * 100) AS pcnt
FROM
(
SELECT  Users.full_name
    ,sum(total) As Total,
    ,sum(case when service < 49 then total else 0 end) AS Productive
 FROM    time_report
    , users
WHERE   time_report.User = users.user_id
    AND date BETWEEN '2011-0502' AND '2011-05-11'
GROUP BY 
    User
)a

Upvotes: 2

Dustin Laine
Dustin Laine

Reputation: 38533

SELECT  Users.full_name
        ,sum(total)
        ,sum(case when service < 49 then total else 0 end) AS Productive
        ,(Round((sum(total)/sum(case when service < 49 then total else 0 end)) * 100)) AS Percent
...

Upvotes: 0

Jason McCreary
Jason McCreary

Reputation: 73011

Unfortunately, derived columns can only be used by name in ORDER BY. That is to say you'd have to duplicate it in your SELECT columns versus being able to say ROUND((Total/Productive) * 100):

SELECT  Users.full_name
        ,sum(total) AS Total
        ,sum(case when service < 49 then total else 0 end) AS Productive
        ,(ROUND(sum(total) / sum(case when service < 49 then total else 0 end)) * 100) AS Percentage
FROM    time_report
        , users
WHERE   time_report.User = users.user_id
        AND date BETWEEN '2011-0502' AND '2011-05-11'
GROUP BY User

Upvotes: 4

Related Questions