how do I deconstruct COUNT()?
- by user151841
I have a view with some joins in it. I'm doing a select from that view with COUNT(*) as one of the columns of the select. I'm surprised by the number it's returning. Note that there is no GROUP BY nor aggregate column statement in the source view that the query is drawing from.
How can I take it apart to see how it arrives at this number? I have three columns in the GROUP BY clause.
SELECT column1, column2, column3, COUNT(*) FROM View GROUP BY column1, column2, column3
I get a result like
+---------+---------+---------+----------+
| column1 | column2 | column3 | COUNT(*) |
+---------+---------+---------+----------+
| value1 | valueA | value_a | 103 |
+---------+---------+---------+----------+
| value2 | valueB | value_b | 56 |
+---------+---------+---------+----------+
etc.
I'd like to see how it arrives at that 103, 26, etc. In other words, I want to run a query that returns 103 rows of something, so that I know that I've expressed the query properly. I'm double-checking my work.
I'm not saying that I think COUNT(*) doesn't work ( I know that "SELECT is not broken" ), what I want to double-check is exactly what I'm expressing in my query, because I think I've expressed the wrong thing, which would be why I'm getting unexpected values. I need to see more what I'm actually directing MySQL to count.
So should I take them one by one, and try out each value in a WHERE clause? In other words, should I do
SELECT column1 FROM View WHERE column1 = 'first_grouped_value'
SELECT column1 FROM View WHERE column1 = 'second_grouped_value'
SELECT column2 FROM View WHERE column1 = 'first_grouped_value'
SELECT column2 FROM View WHERE column1 = 'second_grouped_value'
and see the row count returned matches the COUNT(*) value in the grouped results?
Because of confidentiality, I won't be able to post any of the query or database structure. All I'm asking for is a general technique to see what COUNT(*) is actually counting.
