December 23, 2009 at 8:54 pm
Hi all
I'm new with SQL SERVER. I came across the following error wich I'm having a hard time to solve it.
Error: "Msg 164, Level 15, State 1, Procedure test, Line 11 Each GROUP BY expression must contain at least one column that is not an outer reference."
here is my code.
SELECT 'MonthYear', SUM('Total') AS Total
FROM (SELECT CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar) AS 'MonthYear', SUM(Amount) AS 'Total'
FROM dbo.hardware
GROUP BY CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar)
UNION ALL
SELECT CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar) AS 'MonthYear', SUM(Amount) AS 'Total'
FROM dbo.accesories
GROUP BY CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar)) AS tbtemp
GROUP BY 'monthyear'
Thank you
December 23, 2009 at 8:59 pm
Hi,
Post your code
December 23, 2009 at 9:12 pm
SELECT [MonthYear], SUM([Total]) AS Total
FROM
(
SELECT CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar) AS 'MonthYear',
SUM(Amount) AS 'Total'
FROM dbo.hardware
GROUP BY CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar)
UNION ALL
SELECT CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar) AS 'MonthYear',
SUM(Amount) AS 'Total'
FROM dbo.accesories
GROUP BY CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar)
) AS tbtemp
GROUP BY [monthyear]
December 23, 2009 at 9:33 pm
Get rid of your apostrophes. It thinks you're talking about the string 'MonthYear', not the column within your query.
SELECT MonthYear....
FROM
(
SELECT ... AS MonthYear, ...
)...
GROUP BY MonthYear
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
December 23, 2009 at 9:48 pm
Thanks for your replies
But both give me an Invalid Column name error.
Sorry that part of my code was missing.
Create View "Orders" as
December 23, 2009 at 9:53 pm
Viper5646 (12/23/2009)
Thanks for your repliesBut both give me an Invalid Column name error.
Sorry that part of my code was missing.
Create View "Orders" as
Hi,
you mean,
create view [ORDER]
as
SELECT [MonthYear], SUM([Total]) AS Total
FROM
(
SELECT CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar) AS 'MonthYear',
SUM(Amount) AS 'Total'
FROM dbo.hardware
GROUP BY CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar)
UNION ALL
SELECT CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar) AS 'MonthYear',
SUM(Amount) AS 'Total'
FROM dbo.accesories
GROUP BY CAST(DATENAME(M, Date) AS varchar) + '-' + CAST(YEAR(Date) AS varchar)
) AS tbtemp
GROUP BY [monthyear]
December 23, 2009 at 9:55 pm
I assume you have columns called Date and Amount in each of your tables dbo.hardware and dbo.accessories...
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
December 23, 2009 at 10:06 pm
Thank you SSC Eight & SSC-Enthusiastic for your help.
I have posted SSC Eight's code and it worked 🙂
December 23, 2009 at 10:13 pm
Hi Viper,
Both tell you the same concept which you not made in your code. However you got the concept that’s enough.
December 23, 2009 at 10:16 pm
It's "Arun" and "Rob". You're reading the forum status (like yours is "Forum Newbie")
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
December 24, 2009 at 5:57 am
😀 Sorry about that
Merry Christmas And a Happy New Year
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply