How to show total sums of all transactions next to the value of each individual transaction

Question

The table below shows a customer name alongside the salesperson commission for each transaction. How can I add a column to the table below that will show the total commission amount by person? For example, "Graham Zusi" would still have two rows, one for each unique transaction. However, there would be a third column where his two transactions are summed up. That way it's possible to see the individual transaction amount as well as the overall commission gained from each customer side by side

Thanks for your help.

cust_name   TRANSACTION COMMISSION
Brad Davis  360.0900
Fabian Johnson  277.6802
Jozy Altidor    9.7877
Graham Zusi 19.5650
Graham Zusi 123.3050
Julian Green    32.5585

I've tried using GroupBy and Sum but I can't seem to get it.

SELECT c.CUST_NAME,(s.COMMISSION*o.PURCH_AMT)AS "TRANSACTION COMMISSION"
FROM SALESMAN s, CUSTOMER c, ORDERS o
WHERE
o.SALESMAN_ID=s.SALESMAN_ID
AND
o.CUSTOMER_ID=c.CUSTOMER_ID

What is your MySQL version ? It is a Window function problem. Alternative suggestion is to use [`GROUP BY WITH ROLLUP`](https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html) — Madhur Bhaiya, Jul 16 '19 at 09:35
Please don't use Old comma based Implicit joins and use Modern [Explicit JOIN based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Jul 16 '19 at 09:35

score 0 · Answer 1 · answered Jul 16 '19 at 00:18

0

Here's how you should do it.

SELECT
  c.CUST_NAME,
  sum(s.COMMISSION * o.PURCH_AMT) AS "TRANSACTION COMMISSION"
FROM
  SALESMAN s,
  CUSTOMER c,
  ORDERS o
WHERE
  o.SALESMAN_ID = s.SALESMAN_ID
  AND o.CUSTOMER_ID = c.CUSTOMER_ID
GROUP BY
  c.CUST_NAME

answered Jul 16 '19 at 00:18

Ed Bangga

12,879
4
16
30

Hi Ed - The Group By appears to collapse all the customer names into one row per customer, but the goal is to keep each row as it is but to add a column showing the totals for each individual customer. Maybe I missed something - can you explain your use of GROUP BY? Thanks. – FalconKing Jul 16 '19 at 00:29

score 0 · Answer 2 · answered Jul 16 '19 at 02:11

you can try this approcah.

SELECT
c.CUST_NAME,
O.PURCH_AMT,
s.COMMISSION,
(s.COMMISSION * o.PURCH_AMT) AS "TRANSACTION COMMISSION"
FROM
SALESMAN s
left JOIN ORDERS O ON 
o.SALESMAN_ID = s.SALESMAN_ID
LEFT JOIN CUSTOMER C ON
o.CUSTOMER_ID = c.CUSTOMER_ID
GROUP BY
c.CUST_NAME

if it dont help you, you can come back

How to show total sums of all transactions next to the value of each individual transaction

2 Answers2