2

I have done this query before, but for some reason I always have to dig the answer up. Can someone explain the solution for me so I can finally 'get it'! (thanks!)

Table#1 Employees (employeeID, username)
Table#2 Sales (saleID, employeeID, amount)

Question: List all the employees, along with the total # (count) of sales they have.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
mrblah
  • 99,669
  • 140
  • 310
  • 420

5 Answers5

2

You'd want to select all Employees, and calculate their count of sales for each. Because you'd want all employees in the list, you'd select from the Employees table and either left join to the sales table, or do a subquery to the sales table. Doing this will give you the employees with zero sales in the results as well. In the case of the join, you'd have to group by the employee and count the records in the sales table. For the subquery, there is no group by because your base query will return just 1 row per employee.

select   Employees.EmployeeID, 
         Employees.UserName, 
         CountOfSales = COUNT(SaleID)
from     Employees LEFT JOIN 
         Sales ON Employees.EmployeeID = Sales.EmployeeID
group by Employees.EmployeeID, 
         Employees.UserName
/*
EmployeeID  UserName   CountOfSales
----------- ---------- ------------
2           bill       1
3           larry      0
1           scott      2
Warning: Null value is eliminated by an aggregate or other SET operation.
*/

-- OR --

select   E.*, 
         CountOfSales = (select count(*) 
                         from   sales 
                         where  EmployeeID = E.EmployeeID)
from     Employees E
/*
employeeID  username   CountOfSales
----------- ---------- ------------
1           scott      2
2           bill       1
3           larry      0
*/

Results from each query are based on the sample data below...

create table Employees (employeeID int , username varchar(10)) 
create table Sales (saleID int , employeeID int , amount smallmoney)
go
insert Employees values (1, 'steve'), (2, 'bill'), (3, 'larry')
insert Sales values (1, 1, 23), (2,1,33), (3,2,0)
go
Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
2
select
   e.employeeID
   , e.username
   , count(s.saleID) as'sales count'
   , sum(s.amount) as 'sales $ total'
from
   employees e
left outer join
   sales s
on
   s.employeeID = e.employeeID
group by 
   e.employeeID
   , e.username
RSolberg
  • 26,821
  • 23
  • 116
  • 160
  • your query as stated in the answer will give the following error... Msg 8120, Level 16, State 1, Line 1 Column 'e.employeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Scott Ivey May 14 '09 at 03:01
  • I added a group by clause to the query, that should do the trick. – RSolberg May 14 '09 at 03:04
  • 2
    This doesn't list all employees - even when fixed to remove the two unnecessary MAX operations (and list both name and ID in the GROUP BY clause); it only lists employees who have made at least one sale. The INNER JOIN needs to be a LEFT OUTER JOIN. – Jonathan Leffler May 14 '09 at 03:08
  • @Jonathan - thanks for the reminder on the "max." Completely neglected to think that there might be an employee without sales. Probably wouldn't have wanted to be one of them for this purpose :) – RSolberg May 14 '09 at 07:02
1

You say you want the sum in the title of the question but then say you want the # (count) in the body.

If you want the sum then use the SUM function in SQL.

select Employees.EmployeeID, TotalSales = SUM(amount)
from Employees LEFT JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID
Jonathan Parker
  • 6,705
  • 3
  • 43
  • 54
1

Isn't it this simple, assuming that one sale can only be made by one employee?


Select Employees.username, count(Sales.saleID)
From Employees Left Join Sales on Employees.employeeID = Sales.employeeID
Group by Employees.username

GordyII
  • 7,067
  • 16
  • 51
  • 69
  • "assuming that one sale can only be made by one employee" -- good point. Why, then, do you think it safe to assume username is unique? – onedaywhen May 14 '09 at 07:42
0
select emp.username
      ,isnull((select count(*)
                 from sales
                where sales.employeeid = emp.employeeid),0) as [number of sales]
  from employees emp
Daniel
  • 10,864
  • 22
  • 84
  • 115