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:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.
Join the conversation
1 comment