Is this sql syntax correct?
Can i apply SUM()
within an ISNULL()
.... Consider my fo开发者_如何学编程llowing sql server select statement
SELECT e.Emp_Id,e.Identity_No,e.Emp_Name,case WHEN e.SalaryBasis=1
THEN 'Weekly' ELSE 'Monthly' end as SalaryBasis,e.FixedSalary,
ISNULL(Adv.Daily_Wage,0) as Advance from Employee as e
inner join Designation as d on e.Desig_Id=d.Desig_Id
Left Outer Join Payroll as Adv on e.Emp_Id=Adv.Emp_Id where e.Is_Deleted=0
This Statement Works fine.... But when i apply SUM()
within an ISNULL()
SELECT e.Emp_Id,e.Identity_No,e.Emp_Name,case WHEN e.SalaryBasis=1
THEN 'Weekly' ELSE 'Monthly' end as SalaryBasis,e.FixedSalary,
ISNULL(SUM(Adv.Daily_Wage),0) as Advance from Employee as e
inner join Designation as d on e.Desig_Id=d.Desig_Id
Left Outer Join Payroll as Adv on e.Emp_Id=Adv.Emp_Id
where e.Is_Deleted=0
I got the error,
Column 'Employee.Emp_Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Any suggestion....
You need to GROUP BY the other columns in the select. Something like
SELECT e.Emp_Id,
e.Identity_No,
e.Emp_Name,
case
WHEN e.SalaryBasis=1 THEN 'Weekly'
ELSE 'Monthly'
end as SalaryBasis,e.FixedSalary,
ISNULL(SUM(Adv.Daily_Wage),0) as Advance
from Employee as e inner join
Designation as d on e.Desig_Id=d.Desig_Id Left Outer Join
Payroll as Adv on e.Emp_Id=Adv.Emp_Id
where e.Is_Deleted=0
GROUP BY e.Emp_Id, --This section is what you are missing
e.Identity_No,
e.Emp_Name,
case
WHEN e.SalaryBasis=1 THEN 'Weekly'
ELSE 'Monthly'
end,
e.FixedSalary
Have a look at the definition here
GROUP BY (Transact-SQL)
精彩评论