Sum Commission in a Join Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum Commission in a Join Statement

Author

Topic

Volkof
Starting Member

14 Posts

Posted - 2014-10-28 : 10:19:42

Hello

i have two Tables: Employees (id, first name, last name..)
and Sales(id, value, ..., commission, idsalesperson)
One employee is making many sales and i want to add what commission did he took from his sales. Basically i want to see like this:

Full name(first name + "" + last name)as FullName SUM(Commission)

Can I also sort by year in the same statement the commission he took?
The commision value is a calculated field based upon the value of the transaction.
Many thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 10:29:56

[code]
select e.[first name] + ' ' + e.[last name] as FullName + sum(s.commission) as Commission
from employees e
join sales s
on e.id = s.id
group by e.id
[/code]

I can't see how you can sort by year since there is no date column in your sales table.

Volkof
Starting Member

14 Posts

Posted - 2014-10-28 : 10:54:58

Hy gbritton,

there is a date column in the transaction table, i just didn't write it.

/* where s.transactiondate = 2014
group by e.id */

I think this is it.

Thanks for your help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 11:39:03

The add and order by clause:


select e.[first name] + ' ' + e.[last name] as FullName
     , sum(s.commission) as Commission
     , year(s.transactiondate) as SalesYear
from employees e
join sales s
  on e.id = s.id
group by e.id
group by e.id, year(s.transactiondate)
order by e.id, year(s.transactiondate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources