James Rodix James Rodix - 4 years ago 167
SQL Question

Is invalid in the select list because it is not contained in either an aggregate or Groupby clause

I tried to sum the values based on a condition but when i tried the querey I'm getting an error.

I need to sum the values if the case condition satisfies else I dont want that case condition(Summing the values) to be executed.(To activate the case condition I have set a variable(@rcntInputunit to 2) and the querey should work even when the case is inactive (@rcntInputunit to 1).

The code I tried the querey is shown below.

BEGIN
DECLARE @rcntInputunit AS INT
SET @rcntInputunit =2
CREATE TABLE #MathLogicTable
(
IDNUM INTEGER IDENTITY(1,1),
FORMULA Varchar(160),
INPUTName varchar(160),
AttributeValue Decimal(15,3),
yearmonth varchar(160),
Unit int
)
INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(398)',
46,
'2003:2',
15
)

INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(399)',
3,
'2003:1',
183
)

INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(399)',
85,
'2003:2',
15
)

INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(399)',
12,
'2003:1',
15
)

INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(399)',
41,
'2003:2',
183
)


INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(398)',
12,
'2003:1',
183
)

INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(398)',
10,
'2003:2',
183
)

INSERT INTO #MathLogicTable
VALUES(
'imports(398)+imports(399)',
'imports(398)',
5,
'2003:1',
15
)

Select FORMULA,INPUTName, Case when @rcntInputunit >1 THEN sum(AttributeValue) ELSE AttributeValue END AS Value ,yearmonth
from #MathLogicTable
GROUP BY
FORMULA,
INPUTName,
yearmonth
END
--drop table #MathLogicTable


Can somebody tell me what is the mistake im doing in the querey ?

Answer Source

When you use GROUP BY everything on your select list must be either on the GROUP BY list (i.e. FORMULA, INPUTName, and yearmonth) or be included in an aggregate function.

In your case there is one column, namely AttributeValue that is neither grouped on nor is aggregated.

Select
    FORMULA
,   INPUTName
,   Case when @rcntInputunit >1 THEN 
        sum(AttributeValue)
    ELSE
        AttributeValue -- <<== Here
    END AS Value
,   yearmonth 
from #MathLogicTable
GROUP BY 
    FORMULA
,   INPUTName
,   yearmonth
END

To fix this problem you need to decide which of the many items in a group you would like to provide as the Value column in the result, and move the condition inside SUM:

SUM(
    CASE when @rcntInputunit > 1 OR (you-want-this-row's AttributeValue) THEN 
        AttributeValue
    END
) AS Value
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download