0

I'm getting the following error messages:

Msg 8120, Level 16, State 1, Line 1 Column 'customers.member_category' 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 1 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

How can I order the results by the case?

Thanks

SELECT
Date,
order_id,
member_category,
COUNT(*) AS no_items, 
SUM(item_amount) AS total_amount
FROM 
order_items OI (NOLOCK) JOIN customers C (NOLOCK) 
ON OI.CUSTOMER_NO = C.CUSTOMER_NO
WHERE 
DATE = '01 FEB 2014'
GROUP BY 
order_id, 
member_category
UNION
SELECT
    '',
    '',
'Total',
COUNT(*) AS no_items, 
SUM(item_amount) AS total_amount
FROM 
order_items OI (NOLOCK) JOIN customers C (NOLOCK) 
ON OI.CUSTOMER_NO = C.CUSTOMER_NO
WHERE 
DATE = '01 FEB 2014'
GROUP BY Date WITH ROLLUP
ORDER BY Date ASC, CASE member_category WHEN 'VIP' THEN 1
                    WHEN 'STD' THEN 2                   
                    WHEN 'GLD' THEN 3 
           END
HL8
  • 1,369
  • 17
  • 35
  • 49
  • 1
    You are missing `SELECT` after your `UNION`. You should probably use `UNION ALL` (understand the difference). With regards to the error you posted, you need to follow it's instruction - add the column to the select list – Nick.Mc Mar 21 '14 at 01:43

1 Answers1

0

The second part of your UNION statement does not contain field member_category. This violates basic principle of UNION which is that number and order of columns in each statement should be same.

To resolve the issue, you need to add member_category both in the list of retrieved columns and in the GROUP BY clause.

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • But I don't want to subtotal by member_category. Is there any other way around it? – HL8 Mar 21 '14 at 01:43
  • How about getting rid of the `union` altogether and directly adding `ROLLUP` to your first query? You can refer to this: http://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx – shree.pat18 Mar 21 '14 at 01:51
  • It was much easier with compute. Why did they have to take it away! – HL8 Mar 21 '14 at 02:16