Q

Count two columns in a join

I want to use COUNT on two columns within the same statement. I want to count rows of column1 and column2 where column1 belongs to table1 and column2 to table2.

I want to use COUNT on two columns within the same statement. My query goes like this:

select column1, column2
from table1, table2
where field1.table1 = field2.table2

I want to count rows of column1 and column2 where column1 belongs to table1 and column2 to table2.

At first glance, the answer to this question appears obvious --

select count(table1.column1) as count_column1
     , count(table2.column2) as count_column2
  from table1
inner
  join table2
    on table2.field2 = table1.field1

However, since these counts are in an INNER JOIN, they will count column values only from the joined rows. In other words, if there are any rows of table1 that don't have a matching row in table2, then those values of column1 won't be counted. And vice versa if there are rows in table2 that don't exist in table1.

Another thing to note is that the counts will be exactly the same if both column1 and column2 are NOT NULL. Why? Because COUNT(column), like all aggregate functions except COUNT(*), ignores nulls. And, again, since it's a join, the counts will be identical. The only time they'll be different is when nulls are allowed and actually exist in one or the other column.

If, on the other hand, you're after the overall counts of these two columns from their respective tables, regardless of which rows actually meet the join conditions, then you can get those separate counts in one query like this --

select ( select count(column1)
           from table1 ) as count_column1
     , ( select count(column2)
           from table2 ) as count_column2

Notice that the outer query has no FROM clause; not all databases support this syntax. And, again, remember the difference between COUNT(column) and COUNT(*), and use COUNT(*) if you actually want to count rows instead of non-null values.

This was first published in June 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close