• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

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()))
0
zimmer9
Asked:
zimmer9
  • 2
1 Solution
 
raterusCommented:
You just have to add the column it's talking about, I assume what you said, the Dateadd function to the select list.
0
 
zimmer9Author Commented:
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()))
0
 
zimmer9Author Commented:
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()))
0
 
rafranciscoCommented:
Try this:

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 tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
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()))

I removed this part:

DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()),

because it does not refer to any column from any of the tables.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now