SQL query causing error due to group by statement (INVALID COLUMNS)

Question

I have following SQL:

  DECLARE @EmpId AS Varchar(20),
          @CompanyId as VARCHAR(20),
          @DepartmentId AS VARCHAR(20),
          @DesignationId AS VARCHAR(20),
          @GradeId AS VARCHAR(20),
          @FromDate AS DATE,
          @TillDate AS DATE 

SET @EmpId = null
SET @CompanyId = NULL
SET @DepartmentId = NULL
SET @DesignationId = NULL
SET @GradeId = NULL
SET @FromDate = '1-1-2015'
SET @TillDate = '1-1-2016'

SELECT
    LA.EmpId,AT.IsPaid AS 'IsPaid',
    AT.AttendanceTypeCode, 
    (SELECT DISTINCT IsPaid  
     FROM LeaveApproval 
     WHERE empid = LA.EmpId) AS 'testPAID',
    SUM(CASE WHEN AT.IsPaid = 1 THEN 1 END) AS Paid,
    COUNT(CASE WHEN AT.IsPaid = 0 THEN AT.AttendanceTypeCode END) AS UnPaid,
    COUNT(AT.AttendanceTypeCode) AS LeaveCount
FROM
    LeaveApproval LA
INNER JOIN 
    AttendanceTypes AT ON LA.AttendanceTypeId = AT.AttendanceTypeId
INNER JOIN 
    EmpMaster EM ON LA.Empid = EM.EmpId 
WHERE
    EM.ActiveInActive <> 1 AND 
    EM.EmpId  = CASE WHEN ISNULL(@EmpId ,'-1') <> '-1' AND @EmpId <> ''  
                        THEN @EmpId 
                        ELSE EM.EmpId 
                END
    AND EM.CompanyId = CASE WHEN ISNULL(@CompanyId ,'-1') <> '-1' AND @CompanyId <>     '' 
                              THEN @CompanyId 
                              ELSE EM.CompanyId 
                       END
    AND EM.DepartmentId = CASE WHEN ISNULL(@DepartmentId,'-1') <> '-1' AND @DepartmentId<> ''  
                                 THEN @DepartmentId 
                                 ELSE EM.DepartmentId 
                          END
    AND ISNULL(EM.DesignationId, '')  = CASE WHEN ISNULL(@DesignationId ,'-1') <>  '-1' AND @DesignationId <> '' THEN @DesignationId ELSE ISNULL(EM   .DesignationId,'') END
    AND ISNULL(EM.GradeId,'')  = CASE WHEN ISNULL(@GradeId ,'-1') <> '-1' AND @GradeId <> '' THEN @GradeId ELSE ISNULL(EM .GradeId,'') END
    AND FromDate BETWEEN @FromDate AND  @TillDate 
GROUP BY
    LA.EmpID, AT.AttendanceTypeCode, AT.IsPaid, Paid, UnPaid

The above SQL is not working.

I get an error

Column Paid and InPaid are invalid.

What I know is that 'Paid' and Unpaid are from subquery and are NOT available in select list's group by.

My question is how can I achieve the above outcome after running my SQL my outcome should repeat same value which is sum for Paid for each empid

Any help will be appreciated

Answer 1

DECLARE @EmpId AS Varchar(20),
  @CompanyId as VARCHAR(20),
  @DepartmentId AS VARCHAR(20),
  @DesignationId AS VARCHAR(20),
  @GradeId AS VARCHAR(20),
  @FromDate AS DATE,
  @TillDate AS DATE 



Set @EmpId=null
SET @CompanyId= NULL
SET @DepartmentId=NULL
SET @DesignationId=NULL
SET @GradeId=NULL
SET @FromDate='1-1-2015'
SET @TillDate='1-1-2016';

with LeaveApprovalCTE as
(
Select 
  AT.IsPaid as 'IsPaid',
  AT.AttendanceTypeCode, (select distinct IsPaid from LeaveApproval where empid=LA.EmpId) as 'testPAID',
 CASE WHEN AT.IsPaid=1 Then 1 END as Paid,
CASE WHEN AT.IsPaid=0 Then AT.AttendanceTypeCode  END as UnPaid,
 AT.AttendanceTypeCode AS LeaveCount
 From 
LeaveApproval LA
INNER JOIN AttendanceTypes AT ON LA.AttendanceTypeId = AT.AttendanceTypeId
INNER JOIN EmpMaster EM ON LA.Empid = EM.EmpId 

WHERE
EM.ActiveInActive <> 1 AND 
EM.EmpId  = CASE WHEN ISNULL(@EmpId ,'-1') <> '-1' AND @EmpId <> '' THEN   @EmpId ELSE EM .EmpId END
AND
EM.CompanyId = CASE WHEN ISNULL(@CompanyId ,'-1') <> '-1' AND @CompanyId <>     '' THEN @CompanyId ELSE EM .CompanyId END
AND
EM.DepartmentId = CASE WHEN ISNULL(@DepartmentId,'-1') <> '-1' AND   @DepartmentId<> '' THEN @DepartmentId ELSE EM .DepartmentId END
AND
ISNULL(EM.DesignationId,'')  = CASE WHEN ISNULL(@DesignationId ,'-1') <>  '-1' AND @DesignationId <> '' THEN @DesignationId ELSE ISNULL(EM   .DesignationId,'') END
AND
ISNULL(EM.GradeId,'')  = CASE WHEN ISNULL(@GradeId ,'-1') <> '-1' AND    @GradeId <> '' THEN @GradeId ELSE ISNULL(EM .GradeId,'') END
AND
FromDate BETWEEN @FromDate AND  @TillDate
)

Select 
AttendanceTypeCode,
 SUM(Paid) as paid ,
COUNT(UnPaid) as unpaid,
COUNT(LeaveCount) AS LeaveCount

from LeaveApprovalCTE

Group By AttendanceTypeCode,IsPaid,Paid,UnPaid

Use CTE for add new columns in query . Modify your query like this . hope it will work for you

Answer 2

You cannot use an alias of a column of the current SELECT in its GROUP BY. You have to either do this:

Group By LA.EmpID,AT.AttendanceTypeCode,AT.IsPaid,SUM(CASE WHEN AT.IsPaid=1 Then 1 END), COUNT(CASE WHEN AT.IsPaid=0 Then AT.AttendanceTypeCode END)

Or use all your SELECT as a subquery and group by as you planned in the outer query.