Combine multiple results as columns, not rows

sql, sqlite

Solution

You should be able to use a CROSS JOIN between the queries:

SELECT *
FROM
(
  SELECT Count(Id) As UndeliveredSms
  FROM
  (
   SELECT Id
   FROM IncomingSms
   WHERE Id NOT IN (SELECT IncomingSmsId
                    FROM DeliveryAttempt
                    WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
  )
)
CROSS JOIN
(
  SELECT Count(Id) As UndeliveredEMail FROM
  (
   SELECT Id
   FROM IncomingEMail
   WHERE Id NOT IN (SELECT IncomingEMailId
                    FROM DeliveryAttempt
                    WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
  )
);

See SQL Fiddle with Demo

Problem

I need to perform a number of counts on various tables in the database and I would like to combine those counts into a single result. Conider the following queries: ``` SELECT 100 As SomeCount SELECT 200 As SomeOtherCount SELECT 300 As YetAnotherCount ``` If I combine them using `UNION`, each of the results will be a row in the final result: ``` SELECT 100 As SomeCount UNION SELECT 200 As SomeOtherCount UNION SELECT 300 As YetAnotherCount ``` Output: ``` > SomeCount > --------- > 100 > 200 > 300 ``` What I want instead is ``` > SomeCount | SomeOtherCount | YetAnotherCount > -------------------------------------------- > 100 | 200 | 300 ``` The only other way I could think off to 'name' the results is using something like this: ``` SELECT 'SomeCount' As Name, 100 As Value UNION ALL SELECT 'SomeOtherCount', 200 UNION ALL SELECT 'YetAnotherCount', 300 ``` In which case the result looks like: ``` > Name | Value > --------------------------------- > 'SomeCount' | 100 > 'SomeOtherCount' | 200 > 'YetAnotherCount' | 300 ``` Is there a way to get the results I want, or is the last method the way to go? I should mention the queries above are very simple in order to explain the core problem. In reality two queries that need to be combined might look like this: Query 1: ``` SELECT Count(Id) As UndeliveredSms FROM ( SELECT Id FROM IncomingSms WHERE Id NOT IN (SELECT IncomingSmsId FROM DeliveryAttempt WHERE Status = 'Delivered' OR Status = 'FailedPermanently') ) ``` Query 2: ``` SELECT Count(Id) As UndeliveredEMail FROM ( SELECT Id FROM IncomingEMail WHERE Id NOT IN (SELECT IncomingEMailId FROM DeliveryAttempt WHERE Status = 'Delivered' OR Status = 'FailedPermanently') ) ``` Wrapping these in another `SELECT` statement does not work with SQlite. Using the last method in the examples does work, and I might go with that solution unless it's a bad idea: ``` SELECT 'UndeliveredSms' As Name, Count(Id) As Value FROM ( SELECT Id FROM IncomingSms WHERE Id NOT IN (SELECT IncomingSmsId FROM DeliveryAttempt WHERE Status = 'Delivered' OR Status = 'FailedPermanently') ) UNION SELECT 'UndeliveredEMail', Count(Id) FROM ( SELECT Id FROM IncomingEMail WHERE Id NOT IN (SELECT IncomingEMailId FROM DeliveryAttempt WHERE Status = 'Delivered' OR Status = 'FailedPermanently') ) ``` Which results in something like this: ``` > Name | Value > --------------------------------- > UndeliveredEMail | 82 > UndeliveredSms | 0 ``` And of course, in reality, there are a lot more things to count

Original source