CODEWITHSUNDEEP
sql
user3788316
user3788316

Reputation: 1

Multiply a SUM by another column

I'm trying to multiply a SUM statement with another column. For instance:

SELECT SUM(Orders.Sales)

To get the total sales.

SELECT Employee.Commission_Percentage

To get the commission percentage.

If I want to combine these into

SELECT SUM(Orders.Sales) * Employee.Commission_Percentage AS 'Total Income'

I have to put Employee.Commision_Percentage in the GROUP BY clause. This throws the calculation off. Is there any other way to multiply a SUM by another column result without putting it in an aggregate or GROUP BY?

Upvotes: 0

Views: 491

Answers (2)

Hambone
Hambone

Reputation: 16397

Use a subquery... Something similar to this:

select
  e.employee_id,
  s.sales * e.commission_percentage as total_income
from
  employee e
  join (
    select
      o.employee_id, sum (o.sales) as sales
    from orders.sales as o
    group by o.employee_id ) s on
  e.employee_id = s.employee_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you want the total commissions:

SELECT SUM(o.Sales * e.Commission_Percentage) 
FROM Orders o JOIN
     Employee e
     ON o.EmployeeId = e.EmployeeId;

Upvotes: 2

Related Questions