CODEWITHSUNDEEP
sqlsql-servert-sql
Thimo Franken
Thimo Franken

Reputation: 330

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.

Upvotes: 0

Views: 55

Answers (3)

Pham X. Bach
Pham X. Bach

Reputation: 5432

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'
) AS tmp;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Who can read such a query? Use table aliases!

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

As for getting these in one row, one method is just to use subqueries:

SELECT (SELECT COUNT(b.BOOKINGNUMBER) 
        FROM [fms].[dbo].[Booking] b INNER JOIN
             [fms].[dbo].[Container] c
             ON b.[BOOKINGNUMBER] = c.[BOOKINGNUMBER] INNER JOIN
             [fms].[dbo].[File] f
             ON [c.FILENUMBER = f.FILENUMBER
        WHERE b.RELATIONCODE = 'SHIP02' AND [f.ETA BETWEEN '2000-10-27' AND '2016-10-28'
       ) as fms_count,
       (SELECT COUNT(b.BOOKINGNUMBER) 
        FROM [fmsair].[dbo].[Booking] b INNER JOIN
             [fmsair].[dbo].[Container] c
             ON b.[BOOKINGNUMBER] = c.[BOOKINGNUMBER] INNER JOIN
             [fmsair].[dbo].[File] f
             ON [c.FILENUMBER = f.FILENUMBER
        WHERE b.RELATIONCODE = 'SHIP02' AND [f.ETA BETWEEN '2000-10-27' AND '2016-10-28'
       ) as fmsair_count,
       (SELECT COUNT(b.BOOKINGNUMBER) 
        FROM [fmsprojects].[dbo].[Booking] b INNER JOIN
             [fmsprojects].[dbo].[Container] c
             ON b.[BOOKINGNUMBER] = c.[BOOKINGNUMBER] INNER JOIN
             [fmsprojects].[dbo].[File] f
             ON [c.FILENUMBER = f.FILENUMBER
        WHERE b.RELATIONCODE = 'SHIP02' AND [f.ETA BETWEEN '2000-10-27' AND '2016-10-28'
       ) as fmsprojects_count

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way

SELECT (SELECT Count([fms].[dbo].[Booking].BOOKINGNUMBER)
        FROM   [fms].[dbo].[Booking]
               .......
        WHERE  [fms].[dbo].[Booking].RELATIONCODE = 'SHIP02'
               AND [fms].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28') as fms_count,
       (SELECT Count([fmsAir].[dbo].[Booking].BOOKINGNUMBER)
        FROM   [fmsAir].[dbo].[Booking] 
               .......
        WHERE  [fmsAir].[dbo].[Booking].RELATIONCODE = 'SHIP02'
               AND [fmsAir].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28') as fmsAir_count,
       (SELECT Count([fmsProjects].[dbo].[Booking].BOOKINGNUMBER)
        FROM   [fmsProjects].[dbo].[Booking]
               .....
        WHERE  [fmsProjects].[dbo].[Booking].RELATIONCODE = 'SHIP02'
               AND [fmsProjects].[dbo].[File].ETA BETWEEN '2000-10-27' AND '2016-10-28') as fmsProjects_count

Upvotes: 0

Related Questions