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

Mysql – How to get a percentage of a subcategory in a column

I am using mysql workbench 8.0

I am teaching myself SQL and am currently starting on some guided projects with a LOT of googling. Right now I am working with this data:

Here’s the relevant section I am working with:

Branch Customer Type
A Member
C Normal
A Normal
A Member
B Normal

on and on and on. What I currently have is the following:

Branch Customer Type cust_num
A Member 167
A Normal 173
B Member 165
B Normal 167
C Member 169
C Normal 159

What I want is another column next to the cust_num column that has the percentage of total customers per branch per type, so basically:

Branch Customer Type cust_num percentage
A Member 167 49
A Normal 173 51
B Member 165 50
B Normal 167 50
C Member 169 52
C Normal 159 48

These are the 2 most promising solutions I’ve tried

  1. I actually used this solution with this code:
with rawdata as 
(SELECT branch, `Customer Type`, count(`Customer Type`) as type_count
from sales
group by 1)
groupeddata as
(SELECT `Customer Type`, count(`Customer Type`) as totalcust
from sales
Group by 1
)
SELECT r.branch, r.`Customer Type`, r.type_count, r.type_count/g.totalcust * 100.0 as percentage
from rawdata r
JOIN groupeddata g on r.`customer type` = g.`customer type`
order by 1

That gave me a percentage of I think the percentage of the customer type, but I want the percentage of the customer type within a branch. I’ve also tried putting in branch into the groupeddata and also put it in the JOIN part of the code but then the percentages all changed to 100%

Just in case I was completely overthinking it, I tried

select branch, `customer type`, count(`customer type`) as type_count,
(SELECT count(`customer type`)/sum(`Customer type`)) As percentage from sales
group by 1,2
order by 1

returns null. I know that the problem is the sum(customer type) because it’s not a number, but when I try to switch it to type_count/count(customer type) it gives me an error that type_count doesn’t exist

Tags:

2

Answers


  1. 0

    Use SUM(cust_num) to get the total number of customers of each branch, not count(`Customer Type`). That’s the number of rows, not the number of customers.

    WITH total_cust AS (
    SELECT branch, SUM(cust_num) AS total
    FROM sales
    GROUP BY branch
)
SELECT s.*, ROUND(100 * s.cust_num / t.total) AS percentage
FROM sales AS s
JOIN total_cust AS t ON s.branch = t.branch
ORDER BY s.branch, s.`Customer Type`

    DEMO

    There’s no need for two grouped subqueries. Just join the original table with the one that’s grouped by branch.

    Login or Signup to reply.
  2. 0

    You could do this in a short statement if you use PARTITION BY and calculate the SUM for the Branch on the fly:

    SELECT
  sales.*, 
  ROUND(cust_num/(SUM(cust_num) OVER (PARTITION BY Branch))*100) AS percentage
FROM sales

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search