Problem with sum function and Inner join in MS SQL

[divyakrishnan]

Hi,

I have a problem with the following query

select tbl_employee.E_Name,sum(tbl_Salary.Amt),tbl_Dpt.DeptName from tbl_employee inner join tbl_Salary on tbl_employee.eid = tbl_Salary.eid inner join tbl_Dpt on tbl_Salary.eid = tbl_Dpt.eid where tbl_employee.eid ='E101'

When the query is executed,the following error is displayed

"Column ' tbl_employee.E_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I have tried it using group by having clause,but same error is displaying.

Any one please help me to figure out the problem.

Thanks In advance

[urtrivedi]

If following query does not work, then tbl_employee is not contaning e_name column. check spelling in your structure.

select tbl_employee.E_Name,sum(tbl_Salary.Amt),tbl_Dpt.DeptName 
from tbl_employee 
inner join tbl_Salary on tbl_employee.eid = tbl_Salary.eid 
inner join tbl_Dpt on tbl_Salary.eid = tbl_Dpt.eid 
where tbl_employee.eid ='E101'
group by tbl_employee.E_Name,tbl_Dpt.DeptName

or try query with only department

select sum(tbl_Salary.Amt),tbl_Dpt.DeptName 
from tbl_employee 
inner join tbl_Salary on tbl_employee.eid = tbl_Salary.eid 
inner join tbl_Dpt on tbl_Salary.eid = tbl_Dpt.eid 
where tbl_employee.eid ='E101'
group by tbl_Dpt.DeptName

[divyakrishnan]

Hi trivedi,

Thanks for your response. I tried my query with suggestions you made,still returning the same error. If the sum function is removed the query has no error. The problem is with the sum aggregate function.Please help me to solve it.

Thanks In advance

[Pgmer]

Try this

Select EMpNAME,AMT,DEPTNAME from (SELECT tbl_employee.E_Name as EMpNAME,sum(tbl_Salary.Amt) as AMT,tbl_Dpt.DeptName as DEPTNAME
FROM tbl_employee 
INNER JOIN tbl_Salary ON tbl_employee.eid = tbl_Salary.eid 
INNER JOIN tbl_Dpt ON tbl_Salary.eid = tbl_Dpt.eid 
WHERE tbl_employee.eid ='E101'
GROUP BY tbl_employee.E_Name,tbl_Dpt.DeptName) as A

[Fenrir()]

I don't see why this should fail if your query is as below

SELECT tbl_employee.E_Name,
       SUM(tbl_Salary.Amt) AS [Amount],
       tbl_Dpt.DeptName 
FROM tbl_employee 
INNER JOIN tbl_Salary on tbl_employee.eid = tbl_Salary.eid 
INNER JOIN tbl_Dpt on tbl_Salary.eid = tbl_Dpt.eid 
WHERE tbl_employee.eid ='E101'
GROUP BY tbl_employee.E_Name,
         bl_Dpt.DeptName

if you take out the SUM does it return results?

Hello My Friend Krishnan,

To solve your question you'll have to deal with CREATE VIEW as shown below:

CREATE VIEW tbl_IdTotSal AS
    SELECT tbl_Salary.eid as TotId,
       SUM(tbl_Salary.Amt)as TotSalary
    FROM tbl_Salary
    GROUP BY tbl_Salary.eid
GO
    SELECT tbl_employee.E_Name,
           tbl_IdTotSal.TotSalary
           tbl_Dpt.DeptName
    FROM tbl_employee
     INNER JOIN tbl_IdTotSal ON tbl_IdTotSal.TotId = tbl_employee.eid
     INNER JOIN tbl_Dpt ON tbl_Dpt.eid = tbl_employee.eid
    WHERE tbl_employee.eid = 'E101'

Note: A VIEW always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a VIEW.

After creating this Virtual Table it will be available always when you need it.
If you want to drop that Virtual VIEW Table just do:

DROP VIEW tbl_IdTotSal;

If you want to query again just act as you have that Virtual Table tbl_IdTotSal.
i.e: So you will use the SELECT normally. You just use the CREATE VIEW once.

I wish it helped.

[adam_k]

1st of all this thread is 5 months old. I hope OP found the solution already - if not try with the group by as everybody suggested.

Back to your solution: How would a view resolve an issue with a select statement? A view is used to a)simplify a complicated query, b) hide the complexity of a query, c) control security, but never to resolve a select issue, as it only stores an SQL select statement. To verify read what is the definition of a view: http://msdn.microsoft.com/en-us/library/aa214068(v=sql.80).aspx