Thimo Franken Thimo Franken - 1 month ago 7
SQL Question

SQL Stored Procedure Combine UNION rows into 1 row

I would love to know how to put the results of this query into one row instead of 3 rows it gives back:


SELECT COUNT([fms].[dbo].[Booking].BOOKINGNUMBER) FROM [fms].[dbo].[Booking]
INNER JOIN [fms].[dbo].[Container] ON [fms].[dbo].[Booking].[BOOKINGNUMBER] = [fms].[dbo].[Container].[BOOKINGNUMBER]
INNER JOIN [fms].[dbo].[File] ON [fms].[dbo].[Container].FILENUMBER = [fms].[dbo].[File].FILENUMBER
WHERE [fms].[dbo].[Booking].RELATIONCODE = 'SHIP02' AND [fms].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28'

UNION ALL

SELECT COUNT([fmsAir].[dbo].[Booking].BOOKINGNUMBER) FROM [fmsAir].[dbo].[Booking]
INNER JOIN [fmsAir].[dbo].[Container] ON [fmsAir].[dbo].[Booking].[BOOKINGNUMBER] = [fmsAir].[dbo].[Container].[BOOKINGNUMBER]
INNER JOIN [fmsAir].[dbo].[File] ON [fmsAir].[dbo].[Container].FILENUMBER = [fmsAir].[dbo].[File].FILENUMBER
WHERE [fmsAir].[dbo].[Booking].RELATIONCODE = 'SHIP02' AND [fmsAir].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28'

UNION ALL

SELECT COUNT([fmsProjects].[dbo].[Booking].BOOKINGNUMBER) FROM [fmsProjects].[dbo].[Booking]
INNER JOIN [fmsProjects].[dbo].[Container] ON [fmsProjects].[dbo].[Booking].[BOOKINGNUMBER] = [fmsProjects].[dbo].[Container].[BOOKINGNUMBER]
INNER JOIN [fmsProjects].[dbo].[File] ON [fmsProjects].[dbo].[Container].FILENUMBER = [fmsProjects].[dbo].[File].FILENUMBER
WHERE [fmsProjects].[dbo].[Booking].RELATIONCODE = 'SHIP02' AND [fmsProjects].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28'


Is there a way that I can combine the 3 result rows and count them into 1 row. I am using this inside a stored procedure.

Answer

If you want to SUM all the count of all 3 SELECT queries, You could you this:

SELECT SUM(cnt)
FROM(
   SELECT COUNT([fms].[dbo].[Booking].BOOKINGNUMBER) cnt
   FROM [fms].[dbo].[Booking]
   INNER JOIN [fms].[dbo].[Container] ON [fms].[dbo].[Booking].[BOOKINGNUMBER] = [fms].[dbo].[Container].[BOOKINGNUMBER]
   INNER JOIN [fms].[dbo].[File] ON [fms].[dbo].[Container].FILENUMBER = [fms].[dbo].[File].FILENUMBER
   WHERE [fms].[dbo].[Booking].RELATIONCODE = 'SHIP02' AND [fms].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28'
   UNION ALL
   SELECT COUNT([fmsAir].[dbo].[Booking].BOOKINGNUMBER) 
   FROM [fmsAir].[dbo].[Booking]
   INNER JOIN [fmsAir].[dbo].[Container] ON [fmsAir].[dbo].[Booking].[BOOKINGNUMBER] = [fmsAir].[dbo].[Container].[BOOKINGNUMBER]
   INNER JOIN [fmsAir].[dbo].[File] ON [fmsAir].[dbo].[Container].FILENUMBER = [fmsAir].[dbo].[File].FILENUMBER
   WHERE [fmsAir].[dbo].[Booking].RELATIONCODE = 'SHIP02' AND [fmsAir].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28'
   UNION ALL
   SELECT COUNT([fmsProjects].[dbo].[Booking].BOOKINGNUMBER) 
   FROM [fmsProjects].[dbo].[Booking]
   INNER JOIN [fmsProjects].[dbo].[Container] ON [fmsProjects].[dbo].[Booking].[BOOKINGNUMBER] = [fmsProjects].[dbo].[Container].[BOOKINGNUMBER]
   INNER JOIN [fmsProjects].[dbo].[File] ON [fmsProjects].[dbo].[Container].FILENUMBER = [fmsProjects].[dbo].[File].FILENUMBER
   WHERE [fmsProjects].[dbo].[Booking].RELATIONCODE = 'SHIP02' AND [fmsProjects].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28'
)