mysql - get the percentage of a column using sum and rollup

Question

I have a table like the one below... The data I require them using the query below the table. What I need is to calculate the percentage which the formula is sum(column3) / total(column3) *100 or ((0.3333/0.9999)*100). I have searched but I didn't find anything which wil do it using mysql.. is it even possible? can anyone give me some tips?

+----------+---------+----------+--------+
| column1  | column2 | column3 | percentage |
+----------+---------+---------+------------+
| negative |     1   |  0.3333 |     %      |
| neutral  |     1   |  0.3333 |     %      |
| positive |     1   |  0.3333 |     %      |
+----------+---------+----------+-----------+
| Total    |     3   |  0.9999 |     %      |
+----------+---------+----------+-----------+


SELECT 
    column1_text, 
    sum(column2_number) as 'column2', 
    sum(column3_number) as 'column3', 
    percentage_here as 'percentage' 
FROM table 
GROUP BY column1 ASC WITH ROLLUP

Answer 1

We can use an inline view to calculate the total, and do a join operation.

Something like this:

SELECT t.column1
     , SUM(t.column2_number)  AS `column2`
     , SUM(t.column3_number)  AS `column3`
     , ( 100.0 
       * SUM(t.column3_number)
       / s.col3_tot
       )                      AS `percentage`
  FROM `table` t
 CROSS
  JOIN ( SELECT SUM(q.column3_number) AS col3_tot
           FROM `table` q
       ) s 
 GROUP
    BY t.column1
     , s.col3_tot
 ORDER
    BY t.column1 ASC

MySQL runs the inline view query to materialize derived table s, consisting of one row with total of column3_number.

That row is joined to every row returned from t, so the value col3_tot is available on every row, and we can use it in an expression in the SELECT list.

(I've omitted the WITH ROLLUP clause to make clear that WITH ROLLUP has nothing to do with getting the total, or calculating the percentages.)