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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: