Combining multiple results as columns rather than rows

Question

I need to do some calculations on different tables in a database
and I would like to combine these calculations into one result.

Indicate 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 string in the final result:

 SELECT 100 As SomeCount UNION SELECT 200 As SomeOtherCount UNION SELECT 300 As YetAnotherCount 

Output:

 > SomeCount > --------- > 100 > 200 > 300 

Instead i want

 > SomeCount | SomeOtherCount | YetAnotherCount > -------------------------------------------- > 100 | 200 | 300 

The only other way I could come up with a “name” for the results is something like this:

 SELECT 'SomeCount' As Name, 100 As Value UNION ALL SELECT 'SomeOtherCount', 200 UNION ALL SELECT 'YetAnotherCount', 300 

In this case, the result is as follows:

 > Name | Value > --------------------------------- > 'SomeCount' | 100 > 'SomeOtherCount' | 200 > 'YetAnotherCount' | 300 

Is there a way to get the results I want, or is the last way to move on?

I should mention that the above queries are very simple to explain the main problem. In fact, the two questions that need to be combined may look like this:

Request 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') ) 

Request 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 them in another SELECT does not work with SQlite.

Using the latter method in the examples really works, and I can go with this solution if this is not 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') ) 

The result is something like this:

 > Name | Value > --------------------------------- > UndeliveredEMail | 82 > UndeliveredSms | 0 

And, of course, there are actually many things to count

Answer 1

You can use CROSS JOIN between requests:

 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

Answer 2

I do not understand the use of UNION in your queries, but perhaps I do not understand. Not in joining a subset not in all = not in a subset ...

It seems like this can be simplified to the following:

 select * from ( SELECT Count(Id) As UndeliveredSms FROM IncomingSms WHERE Id NOT IN (SELECT IncomingSmsId FROM DeliveryAttempt WHERE Status = 'Delivered' OR Status = 'FailedPermanently') ) t, ( SELECT Count(Id) As UndeliveredEMail FROM IncomingEMail WHERE Id NOT IN (SELECT IncomingEMailId FROM DeliveryAttempt WHERE Status = 'Delivered' OR Status = 'FailedPermanently') ) t2