x

Error:**ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.**

When I ORDER BY inceptmonth my code works but when I use CASE to show the results on how I need them it shows the error:**ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.** Can anyone advise how I can get around this?

SELECT [Inceptmonth] ,SUM([policysIn])[policysIn] ,SUM([Late])[Late] ,AVG([avgdays])[avgdays] ,CONVERT(VARCHAR,CONVERT (decimal(5,0),SUM([ontime] * 100.0) / SUM([Policysin]))) AS [Success] ,DEPT FROM lee_em.dbo.[MONTHLYA] WHERE dept = 'cargo' GROUP BY INCEPTMONTH,DEPT UNION ALL SELECT 'Grand Total', SUM([policysIn]) ,SUM([Late]) ,AVG ([avgdays]) ,CONVERT(VARCHAR,CONVERT (DECIMAL(5,0),SUM([ontime] * 100.0) / SUM([Policysin]))) AS [Success] ,DEPT FROM lee_em.dbo.[MONTHLYA] WHERE dept = 'CARGO'
GROUP BY DEPT

ORDER BY CASE [Inceptmonth] WHEN 'Jan' then 1 WHEN 'Feb' then 2 WHEN 'Mar' then 3 WHEN 'Apr' then 4 WHEN 'May' then 5 WHEN 'Jun' then 6 WHEN 'Jul' then 7 WHEN 'Aug' then 8 WHEN 'Sep' then 9 WHEN 'Oct' then 10 WHEN 'Nov' then 11 WHEN 'Dec' then 12 END
more ▼

asked Dec 10 '12 at 12:57 PM in Default

n3w2sql gravatar image

n3w2sql
840 12 20 26

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I worked it out sorry for being hasty on asking this but think what I have done works.

SELECT * FROM( SELECT [Inceptmonth] ,SUM([policysIn])[policysIn] ,SUM([Late])[Late] ,AVG([avgdays])[avgdays] ,CONVERT(VARCHAR,CONVERT (decimal(5,0),SUM([ontime] * 100.0) / SUM([Policysin]))) AS [Success] ,DEPT FROM lee_em.dbo.[MONTHLYA] WHERE dept = 'cargo' GROUP BY INCEPTMONTH,DEPT UNION ALL SELECT 'Grand Total', SUM([policysIn]) ,SUM([Late]) ,AVG ([avgdays]) ,CONVERT(VARCHAR,CONVERT (DECIMAL(5,0),SUM([ontime] * 100.0) / SUM([Policysin]))) AS [Success] ,DEPT FROM lee_em.dbo.[MONTHLYA] WHERE dept = 'CARGO'
GROUP BY DEPT )DATA

ORDER BY CASE [Inceptmonth] WHEN 'Jan' then 1 WHEN 'Feb' then 2 WHEN 'Mar' then 3 WHEN 'Apr' then 4 WHEN 'May' then 5 WHEN 'Jun' then 6 WHEN 'Jul' then 7 WHEN 'Aug' then 8 WHEN 'Sep' then 9 WHEN 'Oct' then 10 WHEN 'Nov' then 11 WHEN 'Dec' then 12 WHEN 'GRAND TOTAL' then 13 END
more ▼

answered Dec 10 '12 at 01:02 PM

n3w2sql gravatar image

n3w2sql
840 12 20 26

(comments are locked)
10|1200 characters needed characters left

If you need some kind of mapping to get the data ordered in a certain way, I'd prefer the following query structure:

with SomeData as (
 --some dummy data
 select 'Feb' as InceptMonth, 42 as PolicysIn
 union all select 'Jan', 57
 union all select 'Apr', 120
 union all select 'Feb', 24
)
, MonthOrder as (
 --currently hardcoded, would even be better if coming from table
 select 'Jan' as MonthString, 1 as OrderValue
 union all select 'Feb', 2
 union all select 'Mar', 3
 union all select 'Apr', 4
 union all select 'May', 5
 union all select 'Jun', 6
 union all select 'Jul', 7
 union all select 'Aug', 8
 union all select 'Sep', 9
 union all select 'Oct', 10
 union all select 'Nov', 11
 union all select 'Dec', 12
)
select InceptMonth, SUM(PolicysIn) PolicysIn
from SomeData
inner join MonthOrder on MonthOrder.MonthString = SomeData.InceptMonth
group by InceptMonth, OrderValue
order by OrderValue;
Please note: what I've posted is a simplified version of your situation just to indicate how the query could be improved for readability/maintenance purposes.
more ▼

answered Dec 10 '12 at 02:29 PM

Valentino Vranken gravatar image

Valentino Vranken
1.5k 1 2 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816

asked: Dec 10 '12 at 12:57 PM

Seen: 630 times

Last Updated: Dec 10 '12 at 02:29 PM