CODEWITHSUNDEEP
sqlselectcount
wyattburp86
wyattburp86

Reputation: 81

SQL Select COUNT for Multiple Columns in a Single Query

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?

Upvotes: 3

Views: 19026

Answers (3)

asantaballa
asantaballa

Reputation: 4048

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

Upvotes: 2

VoteyDisciple
VoteyDisciple

Reputation: 37803

If you have any control at all over the requirements, change them. SQL isn't designed to turn rows into columns in the way you'd need to do here. You can do it. You just probably shouldn't.

The best option is to just run two different queries. Those will both be optimized and run quickly, and then in your software you can figure out what to do with those two results.

Next best option:

SELECT SUM(IF(col1='a',1,0)) AS a, SUM(IF(col2='dog',1,0)) AS dog

(Your question doesn't tag a specific database engine, so I went with a MySQL-specific solution here mainly because it seems easiest to read as pseudocode. If you're using a different RDBMS, the code will look different, but will follow the same basic pattern.)

Upvotes: 0

Igor
Igor

Reputation: 62228

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.

Upvotes: 7

Related Questions