Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

Using the SQL Query Analyzer I get the message:GROUP BY expressions must refer to column names that appear in the select list.

Do you know how I can fix the following error:

Server: Msg 164, Level 15, State 1, Line 12
GROUP BY expressions must refer to column names that appear in the select list.

Line 12 is as follows:
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()),


SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE()))
AS DateFromC, tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())) AS DateToC
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))

GROUP BY tblRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()),
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())),
tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()))
Avatar of raterus
raterus
Flag of United States of America image
You just have to add the column it's talking about, I assume what you said, the Dateadd function to the select list.
Avatar of zimmer9
zimmer9
Flag of United States of America image

ASKER

The error on line 12 is the 2nd line below in the GROUP BY clause (which I will display again below)
and is reflected in the SELECT list on the 3rd line "AS DFrom":

SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom,


GROUP BY tblRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()),
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())),
tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()))
Avatar of zimmer9
zimmer9
Flag of United States of America image

ASKER

correction:

The error on line 12 is the 2nd line below in the GROUP BY clause (which I will display again below)
and is reflected in the SELECT list on the 3rd line "AS DFrom":

SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom,


GROUP BY tbMthlRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()),
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())),
tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()))
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial