0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

WITH EMPLOYEE As (
SELECT 1 as EMPLOYEE_ID, 'John' as FirstName, 'Krasinski' as LastName
UNION SELECT 2,    'Alice', 'Kowalska'
UNION SELECT 3,    'Ben', 'Nowak'
UNION SELECT 4,    'Anna', 'Smith')
, SALES AS (
SELECT 1 as Sales_ID, 1 as CUSTOMER_ID, 1 as EMPLOYEE_ID, 10 as PRODUCT_ID, 100 VALUE, 0.05 as commission
UNION SELECT 2,    1, 1,    12,  400, 0.05
UNION SELECT 3,    1, 2,    17,  450, 0.07
UNION SELECT 4,    2, 2,    111, 10, 0.07
UNION SELECT 5,    3, 3,    20,  1000, 0.1 )

Select Distinct 
CONCAT(firstName,' ', lastName) as EmployeeName, 
SUM(commission*value) over(partition by SALES.EMPLOYEE_ID) as EmployeeCommission
from SALES 
left join EMPLOYEE on EMPLOYEE.EMPLOYEE_ID=SALES.EMPLOYEE_ID

fork query

edited sep 15 anonymous

created feb 27 12 conradfrix