Getting Error in Sql Query

Question

i have a query written below. i am getting

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

error when i try to run this . please have look on query written below

SELECT T3.[AcctName],
       SUM(T0.[DebLTotal]/85) AS buget, sum(T0.[DebRLTotal]/85) as 'Current Expenses',T4.[Debit]
FROM OBGT T0
INNER JOIN
    (
    SELECT CASE WHEN T1.[AcctName] LIKE '%Salaries%' THEN 'Salaries'
                WHEN T1.[AcctName] LIKE '%Travel%' THEN 'Travel'
                WHEN T1.[AcctName] LIKE '%Supplies%' THEN 'Supplies'
                WHEN T1.[AcctName] LIKE '%Consultants%' THEN 'Consultants'
                WHEN T1.[AcctName] LIKE '%Patient%' THEN 'Patient Care'
                WHEN T1.[AcctName] LIKE '%Equipment%' THEN 'Equipments' 
                WHEN T1.[AcctName] LIKE '%Expense%' THEN 'Other Expenses' 
                WHEN T1.[AcctName] LIKE '%Alteration%' THEN 'Alterations/Renovations'
                ELSE 'Alterations/Renovations'
           END as [AcctName],
           [AcctCode],[Project]
    from OACT AS T1
    ) as T3 ON T0.[AcctCode] = T3.[AcctCode]
INNER JOIN OBGS T2 ON T0.[Instance] = T2.[AbsId] INNER JOIN JDT1 T4 ON T3.[AcctCode] = T4.[Account] INNER JOIN OASC T5 ON T3.[Project]=T5.[Code]
where T2.[Name] = 'Main Budget 2015'
GROUP BY T3.[AcctName],T2.[Name]

What is it about the error message that you don't understand? — Tab Alleman, Dec 01 '15 at 13:42
The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. — jarlh, Dec 01 '15 at 13:47

score 1 · Accepted Answer · answered Dec 01 '15 at 13:26

1

T4.[Debit] is not in your group by clause. Since there is no aggregate function surrounding T4.[Debit], you get this error.

answered Dec 01 '15 at 13:26

nire

448
2
13

score 1 · Answer 2 · answered Dec 01 '15 at 13:42

Just add T4.[Debit] in group by clause or remove it from select clause . Because Avoiding unaggregated columns in group by leads to indeterminate behaviour.

For more information : Why Must That Column Be Contained in an Aggregate Function or the GROUP BY clause?

SELECT T3.[AcctName], SUM(T0.[DebLTotal]/85) AS buget, 
       sum(T0.[DebRLTotal]/85) as 'Current Expenses',T4.[Debit]
FROM OBGT T0
INNER JOIN
    (
    SELECT CASE WHEN T1.[AcctName] LIKE '%Salaries%' THEN 'Salaries'
                WHEN T1.[AcctName] LIKE '%Travel%' THEN 'Travel'
                WHEN T1.[AcctName] LIKE '%Supplies%' THEN 'Supplies'
                WHEN T1.[AcctName] LIKE '%Consultants%' THEN 'Consultants'
                WHEN T1.[AcctName] LIKE '%Patient%' THEN 'Patient Care'
                WHEN T1.[AcctName] LIKE '%Equipment%' THEN 'Equipments' 
                WHEN T1.[AcctName] LIKE '%Expense%' THEN 'Other Expenses' 
                WHEN T1.[AcctName] LIKE '%Alteration%' THEN 'Alterations/Renovations'
                ELSE 'Alterations/Renovations'
           END as [AcctName],
           [AcctCode],[Project]
    from OACT AS T1
    ) as T3 ON T0.[AcctCode] = T3.[AcctCode]
INNER JOIN OBGS T2 ON T0.[Instance] = T2.[AbsId] 
INNER JOIN JDT1 T4 ON T3.[AcctCode] = T4.[Account] 
INNER JOIN OASC T5 ON T3.[Project]=T5.[Code]
where T2.[Name] = 'Main Budget 2015'
GROUP BY T3.[AcctName],T2.[Name],T4.[Debit]

Getting Error in Sql Query

2 Answers2