How do convert a CASE varchar to INT so I can sum across a row?

Question

I'm trying to produce a table based on bookings for an event. People book themselves onto the event, and specify how many guests they will be bringing with them.

I want along each row in the table to list separately in columns: the booking, the number of guests that the person is bringing with them, and then the sum of the booking + guests (effectively, this third column will be no. of guests +1).

SELECT 'Open day' Event,
       CASE WHEN BOOKINGSTATUS = 'Booked' THEN 1 ELSE 0 END as 'Current Bookings',
       GUESTS as 'Number of Guests',
       SUM ('Current Bookings'+'Number of Guests') as 'Total: Booking & Guests'
WHERE EVENTID = 'xxxxx-111111-22222-xxxxx'

The problem I'm getting is that the '1' value in the 'Current Bookings' column is apparently a VARCHAR. Here's the error message:

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'Current Bookings' to data type int.

How do I convert this to an integer within the CASE statement, and will this then enable the final column to a sum a total for each row?

Thanks for any guidance.

Possible duplicate of [TSQL - Cast string to integer or return default value](http://stackoverflow.com/questions/2000045/tsql-cast-string-to-integer-or-return-default-value) — neer, Jul 28 '16 at 13:09

score 2 · Accepted Answer · answered Jul 28 '16 at 13:13

SUM is a set function, simply do + instead to sum within a row. (But you can't use column aliases there.)

SELECT 'Open day' Event,
       CASE WHEN BOOKINGSTATUS = 'Booked' THEN 1 ELSE 0 END as 'Current Bookings',
       GUESTS as 'Number of Guests',
       CASE WHEN BOOKINGSTATUS = 'Booked' THEN 1 ELSE 0 END + GUESTS as 'Total: Booking & Guests'
WHERE EVENTID = 'xxxxx-111111-22222-xxxxx'

score 0 · Answer 2 · answered Jul 28 '16 at 13:10

Please try

SELECT 
    'Open day' Event, 
    CASE WHEN BOOKINGSTATUS = 'Booked' THEN 1 ELSE 0 END as 'Current Bookings',
    GUESTS as 'Number of Guests',
    SUM ( CASE WHEN BOOKINGSTATUS = 'Booked' THEN 1 ELSE 0 END + GUESTS) as 'Total: Booking & Guests'
WHERE EVENTID = 'xxxxx-111111-22222-xxxxx'

How do convert a CASE varchar to INT so I can sum across a row?

2 Answers2