Join SQL select statements using Union

Question

I am trying to join three select queries using Union All but the error is displayed. It is working fine when I join the first two select statements. As I added the third statement I am not able to execute it.

SQL Query

WITH CTE_BOOKINGS (TotalRecurrance,TotalBookingsWithoutRecurrance,TotalBookingsWithRecurrance,RoomID) AS
(
SELECT 0,count(BK.pkBookingID) as TotalBookingsWithoutRecurrance,0, RM.roomID as RoomID
FROM dbo.tblBooking BK WITH (NOLOCK)
INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
WHERE
BK.deleteBooking=0
AND BDT.UTC_bookingEnd > GETUTCDATE()
AND BI.primaryRoom=1
AND BI.Active=1
AND NOT EXISTS (select * from tblBookingRecurrance where fkBookingID=BK.pkBookingID AND ACTIVE=1)
AND RM.roomID=16867
group by RM.roomID

UNION ALL
 
SELECT count(distinct BR.fkRecurranceID) as TotalRecurrance,0,0, RM.roomID as RoomID
FROM dbo.tblBooking BK WITH (NOLOCK)
INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
INNER JOIN tblBookingRecurrance BR WITH (NOLOCK) ON BK.pkBookingID=BR.fkBookingID
WHERE
BK.deleteBooking=0
AND BDT.UTC_bookingEnd > GETUTCDATE()
AND BI.primaryRoom=1
AND BI.Active=1
AND RM.roomID=16867
AND BR.active=1
group by RM.roomID

UNION ALL

SELECT 0,0,count(BK.pkBookingID) as TotalBookingsWithRecurrance, RM.roomID as RoomID
FROM dbo.tblBooking BK WITH (NOLOCK)
INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
WHERE
BK.deleteBooking=0
AND BDT.UTC_bookingEnd > GETUTCDATE()
AND BI.primaryRoom=1
AND BI.Active=1
AND RM.roomID=16867
group by RM.roomID
)

SELECT  SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID FROM CTE_BOOKINGS Group BY RoomID order by 1 desc

Below error is displayed when I run the query:

Error

Column 'CTE_BOOKINGS.TotalBookingsWithRecurrance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — SMor, Jul 10 '20 at 15:26

critical_error · Accepted Answer · 2020-07-10T18:56:30.933

When using UNION, the column names from the first SELECT is returned for the entire resultset. So, even though you specify "TotalBookingsWithoutRecurrance", "TotalRecurrance", and "TotalBookingsWithRecurrance", your resultset (given your current example) column header will be "TotalRecurrance".

I believe your problem lies in your final SELECT:

SELECT  SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID FROM CTE_BOOKINGS Group BY RoomID order by 1 desc

TotalBookingsWithRecurrance is not being grouped on nor are you performing some sort of an aggregate--hence the error.

To keep it as is you'd need to do:

SELECT SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID 
FROM CTE_BOOKINGS 
GROUP BY RoomID, TotalBookingsWithRecurrance
ORDER BY 1 DESC

However, this will add an additional grouping level that I suspect you don't want.

Also, your columns do not appear to be aligned between your UNIONed statements, which is a must--at the very least a best practice (for me anyway).

I cannot test this, but try doing something like the following:

WITH CTE_BOOKINGS ( RoomID, RecurranceType, Recurrance, UnknownCol1, UnknownCol2  ) 
AS (    
    SELECT 
        RM.roomID AS RoomID,
        'TotalBookingsWithoutRecurrance' AS RecurranceType,
        COUNT ( BK.pkBookingID ) AS Recurrance,
        0 AS UnknownCol1,
        0 AS UnknownCol2
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND NOT EXISTS (select * from tblBookingRecurrance where fkBookingID=BK.pkBookingID AND ACTIVE=1)
        AND RM.roomID=16867
    GROUP BY RM.roomID
    UNION ALL
    SELECT 
        RM.roomID AS RoomID,
        'TotalRecurrance' AS RecurranceType,
        COUNT ( DISTINCT BR.fkRecurranceID ) AS Recurrance,
        0 AS UnknownCol1,
        0 AS UnknownCol2 
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    INNER JOIN tblBookingRecurrance BR WITH (NOLOCK) ON BK.pkBookingID=BR.fkBookingID
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND RM.roomID=16867
        AND BR.active=1
    GROUP BY RM.roomID
    UNION ALL
    SELECT 
        RM.roomID AS RoomID,
        'TotalBookingsWithRecurrance' AS RecurranceType,
        COUNT ( BK.pkBookingID ) AS Recurrance, 
        0 AS UnknownCol1,
        0 AS UnknownCol2
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND RM.roomID=18841
    GROUP BY RM.roomID
)
SELECT
    SUM ( 
        CASE WHEN RecurranceType IN ( 'TotalRecurrance', 'TotalBookingsWithoutRecurrance' ) THEN Recurrance ELSE 0 END 
    ) AS TotalBookings, 
    SUM ( 
        CASE WHEN RecurranceType = 'TotalBookingsWithRecurrance' THEN Recurrance ELSE 0 END 
    ) AS TotalBookingsWithRecurrance, 
    RoomID 
FROM CTE_BOOKINGS
GROUP BY RoomID
ORDER BY 1 DESC;

Tried but an error displayed -> Column 'CTE_BOOKINGS.RecurranceType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'CTE_BOOKINGS.Recurrance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — rAJ, Jul 10 '20 at 17:43
Check out the updated example above. I wish I could test this before posting, but I cannot, so it will be a bit of trial and error. Not the addition of SUM for TotalBookingsWithRecurrance. — critical_error, Jul 10 '20 at 17:46

score 0 · Answer 2 · answered Jul 10 '20 at 15:23

The error you're getting has nothing to do with the UNION. There's an error with the 3rd query. You need to fix that up before you can join it to the other 2.

If you're grouping by a single field, that field will be the only one selectable without an aggregate function. Here's a post explaining that in greater depth.

Looking at your query, you're filtering for a specific roomId, not sure what your exact goal is, but in that case, you shouldn't need a group by roomId.

Join SQL select statements using Union

2 Answers2