COUNT output rows in a joined query in SQL

Question

i've two table :

programmes[id,name,..]
notes[id,note,id_prgm,..]

I actually have a request to know some info about prgm and associated notes :

SELECT p.id, p.name, ...
FROM programmes p
    LEFT OUTER JOIN notes n
    ON (n.id_prgm=p.id AND ...)
WHERE ...
GROUP BY p.id
ORDER BY ...
LIMIT 20

And it work great, but now I want to know how many row this query can give to me (without the LIMIT) So I try to put a COUNT(p.id), but it give me the number of note for each programme. And not the full number of programmes.

So who can i edit this query to know that ?

(mysql with php)

score 0 · Accepted Answer · edited May 23 '17 at 12:28

SELECT p.id, p.name, ...
FROM programmes p
    LEFT OUTER JOIN notes n
    ON (n.id_prgm=p.id AND ...)
WHERE ...
GROUP BY p.id WITH ROLLUP

You will have the count per program and then a NULL, TOTAL COUNT row after that. Also, take care of the following:

When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive. However, you still have some control over sort order. GROUP BY in MySQL sorts results, and you can use explicit ASC and DESC keywords with columns named in the GROUP BY list to specify sort order for individual columns. (The higher-level summary rows added by ROLLUP still appear after the rows from which they are calculated, regardless of the sort order.)

If you want the total count shown in each row, remove the group by:

SELECT p.id, p.name, COUNT(*)
FROM programmes p
    LEFT OUTER JOIN notes n
    ON (n.id_prgm=p.id AND ...)
WHERE ...
ORDER BY ...

If you want the count to appear for each record but keeping the group by, do the following:

SELECT t.*, COUNT(*) FROM (
    SELECT p.id, p.name, ...
    FROM programmes p
        LEFT OUTER JOIN notes n
        ON (n.id_prgm=p.id AND ...)
    WHERE ...
    GROUP BY ...
    ORDER BY ...
) t

The limit clause is not supported within a subquery though, you'll have to find something else.

I think you want to DISTINCT, not GROUP BY. This is not the same thing and should not be mixed up. See my answer to this question for further informations.

The "WITH ROLLUP" is what a looked for, thanks ! Just another question about that : can i have the null row in first ? — Alabate, Apr 22 '13 at 18:59
Hum, I speak too fast, when I use ORDER BY and this solution, I've an error : Incorrect usage of CUBE/ROLLUP and ORDER BY — Alabate, Apr 22 '13 at 19:14
See my side note: add `ASC` or `DESC` to the `GROUP BY` clause. — Sebas, Apr 22 '13 at 19:16
It's not possible in my case (I need to use calculated value to sort my rows and it seem not possible). But I will try others solutions. — Alabate, Apr 22 '13 at 19:34

score 0 · Answer 2 · answered Apr 22 '13 at 18:55

You can make a query that counts the rows of your query (which will be a subquery):

select count(*)
from (select p.id, ... from ... )

Of course you can skip the aggregate functions and the group by to make the subquery faster, just add distinct in your subquery:

select count(*)
from (select distinct p.id, ... from ... )

Hope this helps you

score 0 · Answer 3 · answered Apr 22 '13 at 18:59

with php and mysql you can use SQL_CALC_FOUND_ROWS while using limit in query you just need to run the SELECT FOUND_ROWS() just after your query executes will returns all no of rows found

$query = 'SELECT SQL_CALC_FOUND_ROWS  p.id, p.name, ...
FROM programmes p
LEFT OUTER JOIN notes n
ON (n.id_prgm=p.id AND ...)
WHERE ...
GROUP BY p.id
ORDER BY ...
LIMIT 20';
mysql_query($query);
$query = 'SELECT FOUND_ROWS()';
mysql_query($query);

score 0 · Answer 4 · answered Apr 22 '13 at 19:07

You can have PHP count the rows for you.

Here's how I do it:

$sql = "<your-query>";
$resource = mysql_query($sql);

if (! $resource = mysql_query($sql) ){
    echo "Error reading from table $table";
    die;
}

if (! $num = mysql_num_rows($resource ) ){
    echo "No records found in $table";
}
else {
    echo "$num rows found";
    while($row = mysql_fetch_assoc($resource)){
        //...
        // other stuff you want to do
    }
}

Note: I use mysql_ functions, but there should be a similar way in mysqli_.

COUNT output rows in a joined query in SQL

4 Answers4