how do I deconstruct COUNT()?

Question

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.

Answer 1

How can I take it apart to see how it arrives at this number?

Run this query:

SELECT  column1, column2, column3, COUNT(*) FROM
FROM    (
        /* View definition here */
        SELECT  …
        ) q
GROUP BY
        column1, column2, column3

and compare the results with what your actual query returns.

All I'm asking for is a general technique to see what COUNT(*) is actually counting

I have 11 years of MySQL experience and my eyes have seen some terrible things, but I've never seen it not counting despite being told to COUNT(*).

Answer 2

What has surprised you about the number? What did you expect? What did you actually need to calculate?

Your count will be the number of rows in the full grouping. Image if you want to count hours but grouped by year, month and day. Your count will 24 as this is the number of hours for each day within each month within each year.

Any of these variations should help see the grouping;

SELECT column1, count(*)
FROM view
GROUP BY column1    

SELECT column2, count(*)
FROM view
GROUP BY column2    

SELECT column3, count(*)
FROM view
GROUP BY column3    

SELECT column1, column2, count(*)
FROM view
GROUP BY column1, column2

SELECT column2, column3, count(*)
FROM view
GROUP BY column2, column3

SELECT column1, column3, count(*)
FROM view
GROUP BY column1, column3

Answer 3

This way you should get the 103 rows from the first aggregate:

select * from View where column1 = 'value1' and column2 = 'valueA' and column3 = 'value_a'

Answer 4

This will show the groups as the first 3 columns + the count, then all the columns of the member rows afterward (in no particular order within the group):

SELECT X.*
       ,View.*
FROM (
    SELECT column1, column2, column3, COUNT(*)
    FROM View
    GROUP BY column1, column2, column3 
) AS X (column1, column2, column3, row_count)
INNER JOIN View
    ON View.column1 = X.column1
    AND View.column2 = X.column2
    AND View.column3 = X.column3
ORDER BY X.column1, X.column2, X.column3, X.row_count DESC

Note that this will have some problems with NULLs because of the inner join.