adding alias name in where condition of sql query

Question

I have a query like this

SELECT
  employee_tbl.emp_maxid,
  emp_name AS 'Employee Name',
  Designation_tbl.Des_Name AS Designation,
  emp_LabourID,
  emp_IBAN,
  emp_monthlysalary AS Salary,
  0 AS commission,
  ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) AS Total
FROM dbo.employee_tbl
INNER JOIN dbo.Designation_tbl
  ON Designation_tbl.Des_id = employee_tbl.Des_id
LEFT JOIN SalaryProcessLog_tbl S
  ON S.emp_maxid = employee_tbl.emp_maxid
WHERE (emp_deleted = 0
OR emp_deleted IS NULL)
AND employee_tbl.emp_maxid NOT IN (SELECT
  emp_maxid
FROM SalaryProcessLog_tbl
WHERE Balance = 0)
group by employee_tbl.emp_maxid,dbo.employee_tbl.emp_name,Designation_tbl.Des_Name,
employee_tbl.emp_LabourID, emp_IBAN,emp_monthlysalary

my out put getting like this

in my query i want to add filter result by

Where Total <> 0.. how i can do the same?

Any help is very appriciable

Wrap your query up in a derived table. Add Total condition to main query's WHERE. — jarlh
– jarlh, Commented Mar 26, 2018 at 11:01
One cannot specify a column alias in a WHERE clause because the SELECT column list is logically processed after the WHERE clause. Consequently, one must use other query constructs to do what you want. — Dan Guzman
– Dan Guzman, Commented Mar 26, 2018 at 11:05

jarlh · Accepted Answer · 2018-03-26 11:02:10Z

Wrap your query up in a derived table. Add Total condition to outer query's WHERE.

select *
from
(
    SELECT
      employee_tbl.emp_maxid,
      emp_name AS 'Employee Name',
      Designation_tbl.Des_Name AS Designation,
      emp_LabourID,
      emp_IBAN,
      emp_monthlysalary AS Salary,
      0 AS commission,
      ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) AS Total
    FROM dbo.employee_tbl
    INNER JOIN dbo.Designation_tbl
      ON Designation_tbl.Des_id = employee_tbl.Des_id
    LEFT JOIN SalaryProcessLog_tbl S
      ON S.emp_maxid = employee_tbl.emp_maxid
    WHERE (emp_deleted = 0
    OR emp_deleted IS NULL)
    AND employee_tbl.emp_maxid NOT IN (SELECT
      emp_maxid
    FROM SalaryProcessLog_tbl
    WHERE Balance = 0)
    group by employee_tbl.emp_maxid,dbo.employee_tbl.emp_name,Designation_tbl.Des_Name,
    employee_tbl.emp_LabourID, emp_IBAN,emp_monthlysalary
) dt
WHERE Total <> 0

EzLo · Accepted Answer · 2018-03-26 11:02:29Z

To filter grouping results you should use HAVING.

SELECT
  employee_tbl.emp_maxid,
  emp_name AS 'Employee Name',
  Designation_tbl.Des_Name AS Designation,
  emp_LabourID,
  emp_IBAN,
  emp_monthlysalary AS Salary,
  0 AS commission,
  ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) AS Total
FROM dbo.employee_tbl
INNER JOIN dbo.Designation_tbl
  ON Designation_tbl.Des_id = employee_tbl.Des_id
LEFT JOIN SalaryProcessLog_tbl S
  ON S.emp_maxid = employee_tbl.emp_maxid
WHERE (emp_deleted = 0
OR emp_deleted IS NULL)
AND employee_tbl.emp_maxid NOT IN (SELECT
      emp_maxid
    FROM SalaryProcessLog_tbl
    WHERE Balance = 0)
group by employee_tbl.emp_maxid,dbo.employee_tbl.emp_name,Designation_tbl.Des_Name,
employee_tbl.emp_LabourID, emp_IBAN,emp_monthlysalary

HAVING
    ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) <> 0

Collectives™ on Stack Overflow

adding alias name in where condition of sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related