gazy007 (6/26/2015)

---

Hi ,

Sorry and I am grateful for your help and something I have never understood about aggregate probelm

SELECT

ca.AccountNumber , CompanyName, Value,

SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -4, GETDATE())) THEN (Value ) ELSE 0 END) AS '2011',

SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -3, GETDATE())) THEN (Value ) ELSE 0 END) AS '2012',

SUM( CASE WHEN YEAR( TransactionDate ) = YEAR(DATEADD(YY, -2, GETDATE())) THEN (Value ) ELSE 0 END) AS '2013',

SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -1, GETDATE())) THEN (Value ) ELSE 0 END) AS '2014',

SUM( CASE WHEN YEAR( TransactionDate) = YEAR(GETDATE()) THEN (Value ) ELSE 0 END) '2015',

SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, 1, GETDATE())) THEN (value) ELSE 0 END) AS '2016'

FROM view_SalesStatistics

INNER JOIN CustomerAccount ca

ON view_SalesStatistics.CustomerAccountID = ca.CustomerAccountID

GROUP BY ca.AccountNumber, CompanyName;

I get this error when I run the above query.

Msg 8120, Level 16, State 1, Line 2

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