CODEWITHSUNDEEP
sqlsql-server
Nikul Panchal
Nikul Panchal

Reputation: 711

getting error while convert mysql query to sql server

I am working to convert mysql query to sql query, but in that i am getting error, I don't have much knowledge about the sql server, here i have posted my both query, can anyone please help me to resolve this query, i am getting error 

Each GROUP BY expression must contain at least one column that is not an outer reference.

Mysql Query(Working fine in mysql server) : 

SELECT 'Ending Episodes' as display_name, MonthEnd as MonthStart, MonthEnd as MonthEnd, AVG(FullHHRG) as FullHHRG, 
AVG(LUPAAdj) as LUPAAdj, AVG(OutlierAdd) as OutlierAdd, AVG(UpDownCode) as UpDownCode, AVG(Sequester) as Sequester, 
AVG(NetHHRG) as NetHHRG, count(tb_Episode.id) as total 
FROM tb_Episode 
WHERE EpEnd is not null AND EpEnd >= '01-01-01' AND EpEnd < '01-01-01' AND tb_Episode.CustID = '27' 
GROUP BY display_name, MonthEnd ORDER BY tb_Episode.MonthEnd asc

Converted SQL Query(Getting Error)

SELECT 'EndingEpisodes' as display_name, MonthEnd as MonthStart, MonthEnd as MonthEnd, AVG(FullHHRG) as FullHHRG, 
AVG(LUPAAdj) as LUPAAdj, AVG(OutlierAdd) as OutlierAdd, AVG(UpDownCode) as UpDownCode, AVG(Sequester) as Sequester, 
AVG(NetHHRG) as NetHHRG, count(DISTINCT tb_Episode.id) as total 
FROM tb_Episode 
WHERE EpEnd is not null AND EpEnd >= '01-01-01' AND EpEnd < '01-01-01' AND CustID = '27' 
GROUP BY MonthEnd,FullHHRG,LUPAAdj,OutlierAdd,UpDownCode,Sequester,NetHHRG 
ORDER BY MonthEnd asc

Upvotes: 0

Views: 63

Answers (2)

D-Shih
D-Shih

Reputation: 46229

I think your problem is on group by

SELECT 
    'EndingEpisodes' as display_name, 
    MonthEnd as MonthStart, 
    MonthEnd as MonthEnd, 
    AVG(FullHHRG) as FullHHRG, 
    AVG(LUPAAdj) as LUPAAdj, 
    AVG(OutlierAdd) as OutlierAdd, 
    AVG(UpDownCode) as UpDownCode, 
    AVG(Sequester) as Sequester, 
    AVG(NetHHRG) as NetHHRG, 
    count(DISTINCT tb_Episode.id) as total 
FROM tb_Episode 
WHERE EpEnd is not null AND EpEnd >= '01-01-01' AND EpEnd < '01-01-01' AND tb_Episode.CustID = '27' 
GROUP BY MonthEnd
ORDER BY MonthEnd asc

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270061

I think you only want monthend in the group by:

SELECT 'EndingEpisodes' as display_name, MonthEnd as MonthStart, MonthEnd as MonthEnd,
       AVG(FullHHRG) as FullHHRG, AVG(LUPAAdj) as LUPAAdj, AVG(OutlierAdd) as OutlierAdd,
       AVG(UpDownCode) as UpDownCode, AVG(Sequester) as Sequester, 
       AVG(NetHHRG) as NetHHRG, count(DISTINCT tb_Episode.id) as total 
FROM tb_Episode 
WHERE EpEnd is not null AND
      EpEnd >= '01-01-01' AND
      EpEnd < '01-01-01' AND
     CustID = '27' 
GROUP BY MonthEnd
ORDER BY MonthEnd asc;

SQL Server does not recognize column aliases in the GROUP BY clause, so the problem is that display_name is not recognized. It is a constant, so it doesn't make a difference.

Also note that this version will run in MySQL. You might want to be careful about the date formats.

Upvotes: 3

Related Questions