T.newGuy1620 T.newGuy1620 - 2 months ago 9
SQL Question

How to calculate percentage for number of values in a column in sql?

I have a column called text and another called 'categories' with three values "positive", "negative", "neutral".

How can I calculate the percentage of each of the text values in category?
for example if I had 3 rows, 1 row is positive, 1 row is negative and 1 row is neutral what query will produce 33% positive 33% negative and 33% neutral?

This is the stage I got to...

SELECT COUNT(category), category FROM tweets GROUP BY category

Answer

One way to do it

select category, count, count/total percent
  from 
  (
    select category, count(category) count
      from tweets 
     group by category
  ) c JOIN (
    select count(*) total
      from tweets
  ) t

Output:

+----------+-------+---------+
| category | count | percent |
+----------+-------+---------+
| negative |     1 |  0.3333 |
| neutral  |     1 |  0.3333 |
| positive |     1 |  0.3333 |
+----------+-------+---------+

...would it be possible to return just 33% not 0.3333?

select category, count, round(count / total * 100) percent
  from 
  (
    select category, count(category) count
      from tweets 
     group by category
  ) c JOIN (
    select count(*) total
      from tweets
  ) t
+----------+-------+---------+
| category | count | percent |
+----------+-------+---------+
| negative |     1 |      33 |
| neutral  |     1 |      33 |
| positive |     1 |      33 |
+----------+-------+---------+

If you want to add % you can use do concat(round(count / total * 100), '%') but I would strongly suggest to do it (any sort of formatting) in the client code.