My SQL count is returning many instances of "1"

Question

Here is my query

SELECT * FROM `a` 
INNER JOIN `b` ON(`a`.b_id = `b`.id) 
INNER JOIN `c` ON(`a`.c_id = `c`.id) 
LEFT JOIN d ON d.a_id=a.id 
GROUP BY a.id

This returns 1,500 rows like it should.

Now I'm trying to get a count so I change the top line to SELECT COUNT(*) FROM a. Now I get 1,500 rows of the number 1 as opposed to the number 1,500. How can I return the number 1,500 as opposed to 1,500 1s?

The query is kind of ugly but I am stuck within the confines of PHPActiveRecord. If anyone knows an AR solution that would be nice too, but if not I can always write some SQL. The problem is the PHP AR "count" function basically does SELECT COUNT(*) + the rest of your SQL. This is not playing nice.

Answer 1

If you want to know how many groups there are, this should return 1500. But you should probably learn a little more about groups and wrap your head around what the other answerers are saying.

SELECT COUNT(*) FROM (    
    SELECT 1 as dummy FROM `a` 
    INNER JOIN `b` ON(`a`.b_id = `b`.id) 
    INNER JOIN `c` ON(`a`.c_id = `c`.id) 
    LEFT JOIN d ON d.a_id=a.id 
    GROUP BY a.id
) as T

Daniel Williams had already shared this information and I apologize for not reading it all at first. I still suspect that what you really wanted to do was just this:

SELECT COUNT(*) FROM `a`

Isn't it more than coincidence that they both return the number 1500 per your explanations?

Answer 2

When you use COUNT with GROUP BY, it basically means 'partition the result set in groups based on a given set of values, and return the total number of rows in each group'. This is extremely useful if you want to say, find the value of which is used most frequently in a given column. This same logic applies to all aggregate functions.

---

Consider the following table:

id | val
--------
1  | foo
2  | bar
3  | baz
4  | foo

If you query this with a GROUP BY id, you'll see:

SELECT id, COUNT(*) FROM myTable GROUP BY id
1  | 1
2  | 1
3  | 1
4  | 1

As you can see this indicates that there is one row in each group. This indicates that there is are no records in the table that have the same id values. However, if you group by val, you'll see something different:

SELECT val, COUNT(*) FROM myTable GROUP BY val
bar | 1
baz | 1
foo | 2

This indicates that there are two records in the table with a val value of 'foo'. However, if you drop the GROUP BY entirely:

SELECT COUNT(*) FROM myTable
4

This considers the entire table to be in a single group and counts the number of rows in that group. Note that you can COUNT the total number of distinct values in a particular column, like this:

SELECT COUNT(DISTINCT val) FROM myTable
3

This considers the entire table to be in a single group and counts the number of rows in that group.

Here is a quick demonstration I set up so you can play around with this data set and try it for yourself.

---

If you want your query to just return total number of rows in your table (or in the joins between your tables), simply drop the GROUP BY clause. This will all the rows in the entire result set:

SELECT COUNT(*) FROM `a` 
INNER JOIN `b` ON(`a`.b_id = `b`.id) 
INNER JOIN `c` ON(`a`.c_id = `c`.id) 
LEFT JOIN d ON d.a_id=a.id 

And to return the total number of distinct values of a.id, use this:

SELECT COUNT(DISTINCT a.id) FROM `a` 
INNER JOIN `b` ON(`a`.b_id = `b`.id) 
INNER JOIN `c` ON(`a`.c_id = `c`.id) 
LEFT JOIN d ON d.a_id=a.id 

Answer 3

The straight forward solution is to use a subquery i.e. nested SELECT (see solution below), or use a temporary table.

Solution :

#------------

SELECT COUNT(*) FROM (
    SELECT a.id, a.b_id, a.c_id
        FROM `a`
            INNER JOIN `b` ON ( `a`.b_id = `b`.id )
            INNER JOIN `c` ON ( `a`.c_id = `c`.id )
            LEFT JOIN d ON d.a_id = a.id
        GROUP BY a.id
    ) AS t1
;

#------------

• One nuance here: You should omit unnecessary columns or use alias for 'id' columns and other with duplicating names, because otherwise you would get " Duplicate column name 'id' " error. Because for your query "SELECT * FROM ..." you have column names having 'id' name duplicates:

  id, b_id, c_id, id, id, a_id, ...

  • first id is from a.id, second is from b.id, 3rd is c.id (here "..." indicating may-be some other columns, not mentioned here, but you also shoud use aliases for identical names).

  If you query your select you will get an error like "... Duplicate column name 'id' "

  To avoid it omit unnecessary columns OR use aliases ( see here near 'A select_expr can be given an alias using AS alias_name...' ).

Detailed documentation adout MySQL Subquery here

Answer 4

You are counting how many members exist in your groups when you want the total count of groups?

I would try this instead

SELECT COUNT(DISTINCT a.id)
FROM `a` 
INNER JOIN `b` ON(`a`.b_id = `b`.id) 
INNER JOIN `c` ON(`a`.c_id = `c`.id) 
LEFT JOIN d ON d.a_id=a.id 

If you want the total count of all members of all groups then it is

SELECT COUNT(*)
FROM `a` 
INNER JOIN `b` ON(`a`.b_id = `b`.id) 
INNER JOIN `c` ON(`a`.c_id = `c`.id) 
LEFT JOIN d ON d.a_id=a.id 

Technically if you want the 'rows from returned from the original query' you could do

SELECT COUNT(*) 
FROM (your other query) AS something

but that has poor performance. It is easier to just count the rows that would match what you are looking for.

Groups work by grouping data together and then you can specify queries about those groups. In most SQL systems you can only use aggregation queries about groups, doing something like SELECT * would fail, but MySQL allows it.

If you want a deep understanding of SQL and databases read up on Relational Algebra (it is the basis of SQL)

Specifically for group by clauses: http://en.wikipedia.org/wiki/Relational_algebra#Aggregation