[SQL] Writing a SQL Query that sums 2 columns and then divides one by the other to get a...

My dataset is a list of vendors with a column flagging delivery issues. I want to find out which vendors have the most issues. I thought I could sum both total orders and total issues per Vendor then divide issues by orders to get a percentage. I can get the total orders and total issues like this:

CREATE TABLE vendorsTable (vendorName varchar (20), issueName varchar (20), issueCount int,
orderCount int);
INSERT INTO vendorsTable VALUES
("Amazon", "Damaged Box", 1, 1),
("Temu", "Missing Item", 1, 1),
("Shein", "",0,1),
("Ebay","",0,1),
("Dell", "No Invoice", 1,1),
("Apple", "Missing Item", 1,1),
("Shein", "Damaged Box",1,1),
("Dell", "No Invoice", 1,1),
("Apple", "", 0,1);

SELECT vendorName, SUM(issueCount) as IssueTotal, SUM(orderCount) as OrderTotal
FROM vendorsTable
GROUP BY vendorName
ORDER BY vendorName ASC;

vendorName IssueTotal OrderTotal
-------------------- ----------- -----------
Amazon 3 3
Apple 1 2
Dell 2 2
Ebay 1 4
Shein 2 4
Temu 3 3

(6 rows affected)


But when I add a column column called Percentage to compare them I'm not getting what I expected:

SELECT vendorName, SUM(issueCount/orderCount) AS issueSum,
SUM(orderCount) as orderSum,
(SUM(issueCount/orderCount)/ SUM(orderCount) * 100) as Percentage
FROM vendorsTable
GROUP by vendorName;

vendorName issueSum orderSum Percentage
-------------------- ----------- ----------- -----------
Amazon 3 3 100
Apple 1 2 0
Dell 2 2 100
Ebay 0 4 0
Shein 2 4 0
Temu 3 3 100

(6 rows affected)


Expected Output:

vendorName issueSum orderSum Percentage
-------------------- ----------- ----------- -----------
Amazon 3 3 100
Apple 1 2 50
Dell 2 2 100
Ebay 0 4 0
Shein 2 4 50
Temu 3 3 100


Any help with where I've gone wrong would be much appreciated. I'm not married to do this method either, I'm very open to suggestions

Continue reading...