Nested Select SQL Statement - Help Please!

[SuhailValli]

SELECT 
[Period_Day],sum([Coefficient]) as Day_Coefficient
INTO #DS
FROM 
		(SELECT 
				[Period_Day]
				,[Period_Num]
				,AVG([Coefficient]) as [Coefficient]
		FROM [PDM].[PROFILING].[REF_HH_DEFAULT_USAGE]
		WHERE [Is_Current_Flg] = 1
		GROUP BY [Period_Day],[Period_Num])A
GROUP BY [Period_Day]

Hi All,

I am trying to get the above nested Select statement to work but not having much luck.  I have tried In-database as was as just input but struggling.

I have tried to import the data in and then do the steps in separate queries and then Join up later on but the numbers don't seem to come out correct.

can anyone suggest the best workaround that I may be able to test?

[jdunkerley79]

You have an INTO clause which is making the query generate a new temporary table '#DS'

If you want to run the query with InDB and bring result in I think:

SELECT [Period_Day],sum([Coefficient]) as Day_Coefficient
FROM 
		(SELECT [Period_Day]
				,[Period_Num]
				,AVG([Coefficient]) as [Coefficient]
		FROM [PDM].[PROFILING].[REF_HH_DEFAULT_USAGE]
		WHERE [Is_Current_Flg] = 1
		GROUP BY [Period_Day],[Period_Num]) A
GROUP BY [Period_Day]

should work. 

[SuhailValli]

Hi thanks for the reply.

I did remove the Into (temp table) part but its still not liking the nested select statement. 

I have been trying to get this to work for 2 days now and still no luck and have tried many different ways to try to get the data through :-(

[jdunkerley79 · Accepted solution]

Can you post some sample data so can test with?

I mocked a table on my SQL server and that query worked fine:

[SuhailValli]

Its really weird but I'm sure I tried writing the script exactly as you have and had no joy but when I tried today using your example it has worked!  I think I was missing something really simple but not sure what it was!  thank you so much for a swift response :)