CODEWITHSUNDEEP
sqlite
user2366330
user2366330

Reputation: 55

How to calculate Percentage by group of columns

I could not able to write a query to calculate percentage by grouping columns.

Itemname,value
A,3
B,2
A,7
B,8

Output should be

itemname,value,percentage
A,3,30
A,7,70
B,2,20
B,8,80

Percentage is calculated with the group by itemname (ex A).

Upvotes: 1

Views: 2642

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Try this:

CREATE TABLE my_tab (
  Itemname VARCHAR(1),
  value INT
);

INSERT INTO my_tab VALUES ('A', 3);
INSERT INTO my_tab VALUES ('B', 2);
INSERT INTO my_tab VALUES ('A', 7);
INSERT INTO my_tab VALUES ('B', 8);

SELECT
    mt.itemname,
    mt.value,
    mt.value * 100 / agg.value_sum AS percentage
FROM
    my_tab mt
      JOIN (SELECT
                itemname,
                SUM(value) AS value_sum
              FROM my_tab
            GROUP BY itemname
      ) agg ON (mt.itemname = agg.itemname)
;

Check at SQLFiddle: SQLFiddle Example

Output:

Itemname    value    percentage
A               3            30
A               7            70
B               2            20
B               8            80

Upvotes: 3

Related Questions