CODEWITHSUNDEEP
sqlsql-serverms-access
user521128
user521128

Reputation: 23

SQL aggregate functions and Group By

Employee
empId
empName
empStoreNum

Invoice
invNo
invAmount
empId

I have two tables above Employee and Invoice. I would like to setup a query to retrieve employee names, employee store numbers, and the total sales for each employee. I have issue a query below and it works but I was not able to retrieve employee store number.

SELECT Emp.empName, Sum(Inv.invAmount) AS totalSales
  FROM Invoice AS Inv INNER JOIN Employee AS Emp ON Inv.empId = Emp.empId
 GROUP BY Emp.empName

If I add Emp.empStoreNum to the SELECT I get the following error: “You tried to execute a query that does not include the specified expression ‘empStoreNum’ as part of an aggregate function.” How can modify the query to get employee store number also?

Upvotes: 1

Views: 1449

Answers (3)

Nalaka526
Nalaka526

Reputation: 11474

Try adding empStoreNum to GROUP BY

SELECT Emp.empName, Emp.empStoreNum, Sum(Inv.invAmount) AS totalSales
FROM Invoice AS Inv INNER JOIN Employee AS Emp ON Inv.empId = Emp.empId
GROUP BY Emp.empName, Emp.empStoreNum

Upvotes: 2

TGH
TGH

Reputation: 39278

Add that second column to your group by

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

All non-aggregate columns in the select-list must be listed in the GROUP BY clause (unless you're using MySQL, which plays by a very different set of rules, or unless you are using a sufficiently recent version of PostgreSQL, which is able to deduce functional dependencies).

SELECT Emp.empName, Emp.empStoreNum, Sum(Inv.invAmount) AS totalSales
  FROM Invoice AS Inv INNER JOIN Employee AS Emp ON Inv.empId = Emp.empId
 GROUP BY Emp.empName, Emp.empStoreNum

Upvotes: 2

Related Questions