CODEWITHSUNDEEP
mysqlsqlgroup-bysum
wingswingswings
wingswingswings

Reputation: 15

mysql Calculate sum IF by Group

mysql5.7
I have table below and I'm attempting to create calculated table of qty*price and distributor commissions (if total qty>100, 4% commissions, else 2%) grouped by product_name

Id   product_name        quantity price
1    Alpha               100      6000
2    Bravo               80       5500
3    Alpha               70       6000
4    Charlie             41       5500
5    Delta               12       3000
6    Echo                20       3100
7    Echo                30       3200
8    Delta               90       3300
9    Bravo               100      5500

SELECT product_name,
    SUM(quantity*price) as 'Value (qty x price)',
    IF(SUM(quantity)>100,".04",".02")*(quantity*price) as 'Distributor Commission'
FROM transaction_details
GROUP BY product_name;

From the code below I have successfully calculated the 'Value (qty x price)' HOWEVER for 'Distributor Commission' my code seems to take the first line found in the table of product_name, instead of the actual TOTAL or sum(quantity)

How can I write it so that it calculates the total by the group?

Upvotes: 0

Views: 539

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

from the discussion of the comments it seems you want below

    SELECT product_name,
    SUM(quantity*price) as 'Value (qty x price)',
    sum(IF( 
     (select SUM(quantity) 
       )>100,.04,.02)*  (quantity*price)) as 'Distributor Commission'
FROM transaction_details a
GROUP BY product_name;

Upvotes: 0

GMB
GMB

Reputation: 222402

I think you want:

SELECT product_name,
    SUM(quantity*price) as `Value (qty x price)`,
    CASE WHEN SUM(quantity) > 100 THEN 0.04 ELSE 0.02 END 
        * SUM(quantity * price) as `Distributor Commission`
FROM transaction_details
GROUP BY product_name;

Rationale:

  • you want to compute the commission over on the total sales value (so you need a SUM() on the right side of the multiplication)

  • identifiers (such as column aliases for example) should be surrounded with backticks rather than single quotes

  • I rewrote the IF condition as a CASE expression instead (although both are valid in MySQL, I like CASE better, because it is standard SQL)

Upvotes: 1

Related Questions