mysql Calculate sum IF by Group

Question

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?

Two answers, one assuming "total qty" meant total for that product, and the other I think total over all products, though the second answer was incomplete — ysth
– ysth, Commented Jul 28, 2020 at 8:03

GMB · Accepted Answer · 2020-07-28 08:00:29Z

1

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)

edited Jul 28, 2020 at 8:00

answered Jul 28, 2020 at 7:56

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

wingswingswings Over a year ago

That is correct, thank YOu. But, how is this different with the use of IF?

ysth Over a year ago

Maybe explain? You made two changes, one of which was just stylistic, which may confuse

GMB Over a year ago

@ysth: yes I was typing... I just did.

wingswingswings Over a year ago

@GMB appreciate the clarity!!

Gordon Linoff Over a year ago

@ysth . . . case is not stylistic. It is standard SQL. if is a bespoke extension in MySQL.

|

Zaynul Abadin Tuhin · Accepted Answer · 2020-07-28 08:20:22Z

0

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;

edited Jul 28, 2020 at 8:20

answered Jul 28, 2020 at 7:58

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

mysql Calculate sum IF by Group

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related