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