getting error while convert mysql query to sql server

Question

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

Possible duplicate of SQL error when using Group By: Each GROUP BY expression must contain at least one column that is not an outer reference — Hasan Fathi
– Hasan Fathi, Commented Feb 5, 2018 at 12:34

Gordon Linoff · Accepted Answer · 2018-02-05 12:29:54Z

3

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.

answered Feb 5, 2018 at 12:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D-Shih · Accepted Answer · 2018-02-05 12:38:23Z

2

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

edited Feb 5, 2018 at 12:38

answered Feb 5, 2018 at 12:31

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

1 Comment

D-Shih Over a year ago

You're welcome :). I am sometime confusing the syntax when,I changed between mysql and mssql .

Collectives™ on Stack Overflow

getting error while convert mysql query to sql server

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related