ORDER BY items must appear in the select list if t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ORDER BY items must appear in the select list if t

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-31 : 07:52:42

Hi, if i remove the order by it will work, however i need order by.
Thanks:


select  CONVERT(VARCHAR,SUM(Admissions+CoolAdmissions)) as Cinema_DisplayName 
,null AS Admissions,null AS CoolAdmissions,null as Percentage from vistaho.dbo.ZZ_vwpProgressBoard
--ORDER BY MAX(Cinema_Order)
Union
--Total Admissions \ Cinema     
SELECT Cinema_DisplayName as Cinema_DisplayName, 
       Sum(Admissions) AS Admissions,
       Sum(CoolAdmissions) AS CoolAdmissions,
	--    (Count(Admissions+CoolAdmissions)* 100 / (Select Count(*) From ZZ_vwpProgressBoard)) as total
	LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),5) ----- an thes to %  ------ + '%' 
	as Percentage
	  -- ,Sum(Admissions+CoolAdmissions) * 100.0 / Sum(Admissions) over () as percentage
	--  Admissions * 100.0 / Sum(Admissions) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName
ORDER BY MAX(Cinema_Order)

mhorseman
Starting Member

44 Posts

Posted - 2014-10-31 : 08:13:57

Are you sure the UNION is working? You need to have the same number of columns in both parts of your UNION statement - at present you only have 2 in the first part. Once you've got that sorted out, you only need 1 ORDER BY at the end of your query - probaby on whatever you're calling your MAX(Cinema_Order) column.

Mark

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-31 : 09:08:14

Hi. I see 4. What do you mean by 2? I have the convert and the 3 nulls as columns. The union is working fine if i exclude the ORDER BY statement.

mhorseman
Starting Member

44 Posts

Posted - 2014-10-31 : 12:08:23

You're quite right about the number of columns, I read the query wrongly and missed the NULLs.

With a UNION you need to ORDER BY the name of a column you've got in your query - you will need to add a Cinema_Order column to both parts if you want to use that.

Mark

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-31 : 19:25:28

Well i tried and i get:
Column 'dbo.ZZ_vwpProgressBoard.Cinema_Order' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 104, Level 16, State 1, Line 16
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

 
select  CONVERT(VARCHAR,SUM(Admissions+CoolAdmissions)) as Cinema_DisplayName 
,null AS Admissions,null AS CoolAdmissions,null as Percentage,Cinema_Order from dbo.ZZ_vwpProgressBoard
--GROUP BY MAX(Cinema_Order)
Union
--Total Admissions \ Cinema     
SELECT Cinema_DisplayName as Cinema_DisplayName, 
       Sum(Admissions) AS Admissions,
       Sum(CoolAdmissions) AS CoolAdmissions,
	--    (Count(Admissions+CoolAdmissions)* 100 / (Select Count(*) From ZZ_vwpProgressBoard)) as total
	LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),5) ----- an thes to %  ------ + '%' 
	as Percentage, Cinema_Order
	  -- ,Sum(Admissions+CoolAdmissions) * 100.0 / Sum(Admissions) over () as percentage
	--  Admissions * 100.0 / Sum(Admissions) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName
ORDER BY MAX(Cinema_Order)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-01 : 08:02:10

Fundamental to grouping. Each column must either be aggregated or in the group by clause

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-02 : 14:11:31

Ok, so something like this?


select  CONVERT(VARCHAR,SUM(Admissions+CoolAdmissions)) as Cinema_DisplayName 
,null AS Admissions,null AS CoolAdmissions,null as Percentage, MAX(Cinema_Order)  from dbo.ZZ_vwpProgressBoard
--GROUP BY MAX(Cinema_Order)
Union
--Total Admissions \ Cinema     
SELECT Cinema_DisplayName as Cinema_DisplayName, 
       Sum(Admissions) AS Admissions,
       Sum(CoolAdmissions) AS CoolAdmissions,	
	LEFT(sum(admissions + CoolAdmissions) * 100.0 / (select sum(Admissions + CoolAdmissions) from ZZ_vwpProgressBoard),5)  + '%' 
	as Percentage, Cinema_Order  as Cinema_Order	
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName,Cinema_Order
ORDER BY  MAX(Cinema_Order)

However i have an issue here since the firs part of the union would not appear first or last
so i have:
Cinema1 1330 0 25.22% 1
Cinema2L 1006 0 20.57% 2
.....etc
CinemaR 140 0 1.892% 10
17970 NULL NULL NULL 11 ---- This should display either first or last.
CinemaR2 616 0 14.55% 11

.Any help?Mind you i need the ORDER BY MAX(Cinema_Order) as the first Ordey By always.
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-02 : 14:52:14

Aren't you mixing up the columns in the union? I mean

CONVERT(VARCHAR,SUM(Admissions+CoolAdmissions)) as Cinema_DisplayName -- first part of union

is not analogous to

Cinema_DisplayName as Cinema_DisplayName, -- second part of union

regardless, why should the line with 17970 be either first or last? According to the ORDER BY, it's right where it should be, isn't it? Do you need to add an additional column to the ORDER BY?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-02 : 18:24:49

Hi. I would like to have the line first or last as i will be taking the result and using them in .net code. It is not crucial to do so but it could save some calculations on the .net part in order to calculate the exact location of the first union part(if i know it will pop up first or last).
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources