CODEWITHSUNDEEP
mysqlsqlpercentage
user3574776
user3574776

Reputation:

SQL - Calculate percentage of filtered SUM fields

I have a table where I store sales made by a localshop, divided by type of sale and year and grouped by the name of the client (there are just a few clients). The user passes these parameters via JavaScript and I'm supposed to return all the sales made in the selected year grouped by type of sale.

The current SQL statement returns all the sales grouped by the different clients in these year that belongs to the first type of sale (which is a "general" sale), but I would like to retrieve also which percentage of the selected sale belongs to the amount of each user.

The table:

CREATE TABLE `sales` (
    `id_sale` INT(11) NOT NULL,
    `name_of_client` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
    `type_of_sale` TINYINT(1) NOT NULL,
    `year` YEAR NOT NULL,
    `amount` DOUBLE(22,0) NOT NULL,
    PRIMARY KEY (`id_sale`) USING BTREE
)

SQL Statement:

SELECT name_of_client, SUM(amount) 
FROM sales 
WHERE type_of_sale = 1 AND year = 2020 
GROUP BY name_of_client 
ORDER BY SUM(amount) DESC

I've tried other suggested solutions in SO threads, but they calculate the percentage of ALL sales in the table, and I need the percentage of the SUM(amount) for each client in the current year and with the current sale type.

Upvotes: 2

Views: 227

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If I understand correctly, this is just conditional aggregation:

SELECT name_of_client,
       SUM(CASE WHEN type_of_sale = 1 THEN amount ELSE 0 END) / SUM(amount)
FROM sales
WHERE year = 2020
GROUP BY name_of_client
ORDER BY SUM(amount) DESC;

If you want this per year:

SELECT name_of_client, year,
       SUM(CASE WHEN type_of_sale = 1 THEN amount ELSE 0 END) / SUM(amount)
FROM sales
GROUP BY name_of_client, year
ORDER BY SUM(amount) DESC

Upvotes: 2

Related Questions