SQL Select COUNT for Multiple Columns in a Single Query

Question

I am relatively new to SQL and have only a working knowledge of the language, but I've been tasked with creating a table that provides a summary of a set of data I'm working with in another table. Specifically, I am working with a table that looks like this (apologies for formatting of table as I couldn't get it to display properly vertically):

Table 1

Col1: a, b, c, d, a, d, b

Col2: dog, cat, dog, cat, horse, bird, cat

And, I want to use SELECT COUNT to output the number of unique occurrences of "a" in one column and then the number of unique occurrences of "dog" in a second column (they are not dependent on each other in the environment we're working in). The new summary table would look like this:

Col1 (# of "a"): 2

Col2 ("# of dogs"): 2

My select statements look something like this:

SELECT COUNT (Col1) as '# of a' FROM "Table 1" WHERE Col1 = 'a' 

And this for the second column:

SELECT COUNT (Col2) as '# of dogs' FROM "Table 1" WHERE Col2 = 'dog'

How can I combine the two SELECT COUNT queries to get the desired table?

Answer 1

You could do this if your sql platform supports the CASE statement.

SELECT SUM(CASE WHEN Col1 = 'a' THEN 1 ELSE 0 END) as '# of a'
    ,  SUM(CASE WHEN Col2 = 'dog' THEN 1 ELSE 0 END) as '# of dogs'
FROM "Table 1"

The above query uses SUM instead of COUNT and the inner expression returns 0 or 1 depending on if the condition evaluates to false or true.

Answer 2

Could just combine the two select as items in another select. Not elegant, but easy to implement.

Select
  cntA     = (SELECT COUNT (Col1) as '# of a' FROM "Table 1" WHERE Col1 = 'a' )
, cntDog   = (SELECT COUNT (Col2) as '# of dogs' FROM "Table 1" WHERE Col2 = 'dog')