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, 2012 at 12:57 PM in Default

avatar image

n3w2sql
870 22 32 39

(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, 2012 at 01:02 PM

avatar image

n3w2sql
870 22 32 39

(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, 2012 at 02:29 PM

avatar image

Valentino Vranken
1.5k 2 4 12

(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.

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:

x2091

asked: Dec 10, 2012 at 12:57 PM

Seen: 1218 times

Last Updated: Dec 10, 2012 at 02:29 PM

Copyright 2016 Redgate Software. Privacy Policy