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.

Be careful! MySQL views with aggregate functions (like `count()`) perform really poorly as the number of rows goes up. This is because of the view algorithm used which causes many indexes to be unusable. — Peter Bailey, May 21 '10 at 14:38
There isn't an aggregate column in the view itself, but I am using an aggregate column in the SELECT of the view. Is this a problem? — user151841, May 21 '10 at 14:45
You said the view has `count(*)` so yes, you are using an aggregate function in the view. More detail here http://stackoverflow.com/questions/2760475/mysql-view-performance/2760603#2760603 — Peter Bailey, May 21 '10 at 14:53
Peter, I didn't say that. "I'm doing a **select** *from that view* with COUNT(*) as one of the columns. ", meaning, "count(*) as one of the columns *of that select*". I just now edited the text to make it more clear. — user151841, May 21 '10 at 14:54
Sorry to come off as hostile and defensive :P I expressed myself ambiguously. The way you read it was reasonable :) — user151841, May 21 '10 at 15:03

score 1 · Accepted Answer · answered May 21 '10 at 14:50

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

score 0 · Answer 2 · answered May 21 '10 at 14:43

0

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(*).

answered May 21 '10 at 14:43

Quassnoi

413,100
91
616
614

this is exactly what I have now. What I want to know is how it arrived a those COUNT(*) values. – user151841 May 21 '10 at 14:46
@user151841: sorry, I don't quite get your question :) If you don't trust `MySQL`, well, count the records yourself of write a `PHP` script that would group and count. BTW, why don't you trust the `COUNT(*)` results? Are they too large, too small, of what? – Quassnoi May 21 '10 at 14:49
The numbers are too large. I trust that COUNT is counting *something* accurately. I don't trust what *I've expressed* in the query. I need to see more what I'm actually directing MySQL to count. – user151841 May 21 '10 at 14:53
@user: replace the stored view with the inline view (as shown in the post) and do the debugging by adding and removing the conditions. – Quassnoi May 21 '10 at 15:00

score 0 · Answer 3 · answered May 21 '10 at 17:35

0

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'

answered May 21 '10 at 17:35

Marek

7,337
1
22
33

score 0 · Answer 4 · answered May 21 '10 at 17:41

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.

how do I deconstruct COUNT()?

4 Answers4