Mysql COUNT(*) on multiple tables

Question

What's wrong with this query:

SELECT co.*, mod.COUNT(*) as moduleCount, vid.COUNT(*) as vidCount 
 FROM courses as co, modules as mod, videos as vid 
 WHERE mod.course_id=co.id AND vid.course_id=co.id ORDER BY co.id DESC

In other words, how can I do it so with every record returned from 'courses', there's an additional column called 'modCount' which shows the number of records in the modules table for that course_id, and another called 'vidCount' which does the same thing for the videos table.

Error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as moduleCount, vid.COUNT() as vidCount FROM courses as co, ' at line 1

Stefan Gehrig · Accepted Answer · 2009-06-03 11:32:38Z

52

Using subselects you can do:

SELECT co.*, 
    (SELECT COUNT(*) FROM modules mod WHERE mod.course_id=co.id) AS moduleCount, 
    (SELECT COUNT(*) FROM videos vid WHERE vid.course_id=co.id) AS vidCount
FROM courses AS co
ORDER BY co.id DESC

But be carefull as this is an expensive query when courses has many rows.

EDIT: If your tables are quite large the following query should perform much better (in favor of being more complex to read and understand).

SELECT co.*, 
    COALESCE(mod.moduleCount,0) AS moduleCount,
    COALESCE(vid.vidCount,0) AS vidCount
FROM courses AS co
    LEFT JOIN (
            SELECT COUNT(*) AS moduleCount, course_id AS courseId 
            FROM modules
            GROUP BY course_id
        ) AS mod
        ON mod.courseId = co.id
    LEFT JOIN (
            SELECT COUNT(*) AS vidCount, course_id AS courseId 
            FROM videos
            GROUP BY course_id
        ) AS vid
        ON vid.courseId = co.id
ORDER BY co.id DESC

edited Jun 3, 2009 at 11:32

answered Jun 3, 2009 at 10:53

Stefan Gehrig

83.9k24 gold badges162 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cori Over a year ago

the expense is an important thing to keep in mind, especially with several subselects.

Ali Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod WHERE mod.course_id=co.id) as moduleCount, (SELECT COUNT(*) ' at line 2

Stefan Gehrig Over a year ago

There was a "WHERE" left that didn't belong in there. Pleas have another try. If this doesn't work, your MySQL server does not support scalar subselects. Which version do you use?

Ali Over a year ago

I think i'm using mysql 5. I did try it without the where but it still didn't work

Stefan Gehrig Over a year ago

MySQL 5 should be OK. Are you running ANSI mode?

|

wasim · Accepted Answer · 2011-10-19 21:41:30Z

14

i have better solution and easy

SELECT COUNT(*),(SELECT COUNT(*) FROM table2) FROM table1

answered Oct 19, 2011 at 21:41

wasim

1411 silver badge2 bronze badges

Comments

Quassnoi · Accepted Answer · 2009-06-03 10:50:52Z

3

SELECT co.*,
       (
       SELECT  COUNT(*)
       FROM    modules mod
       WHERE   mod.course_id = co.id
       ) AS modCount,
       (
       SELECT  COUNT(*)
       FROM    videos vid
       WHERE   vid.course_id = co.id
       ) AS vidCount
FROM   courses co
ORDER BY
        co.id DESC

answered Jun 3, 2009 at 10:50

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Comments

Dave Markle · Accepted Answer · 2009-06-03 10:51:06Z

1

SELECT co.*, m.ModCnt as moduleCount, v.VidCnt as vidCount 
FROM courses co
INNER JOIN (
        select count(*) AS ModCnt, co.id AS CoID
        from modules 
        group by co) m
    ON m.CoID = co.id
INNER JOIN (
        select count(*) AS VidCnt, co.id AS CoID
        from videos
        group by co) v
    ON v.CoID = co.id   
INNER JOIN videos vid 
    ON vid.course_id = co.id 
ORDER BY co.id DESC

answered Jun 3, 2009 at 10:51

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

1 Comment

Ali Over a year ago

I just noticed this, modcnt, lol. Are you allowed to use 'cnt' as a shorthand in work related code? :D

Ali · Accepted Answer · 2009-06-03 11:05:21Z

-2

Shoot this. I did the job with some non-mysql code:

function getAllWithStats($info='*',$order='',$id=0)
{
    $courses=$this->getAll($info,$order,$id);

    foreach ($courses as $k=>$v)
    {
        $courses[$k]['modCount']=$this->getModuleCount($v['id']);
        $courses[$k]['vidCount']=$this->getVideoCount($v['id']);
    }

    return $courses;
}

answered Jun 3, 2009 at 11:05

Ali

268k270 gold badges593 silver badges788 bronze badges

2 Comments

Stefan Gehrig Over a year ago

This is essentially the same as the subselect-solution. You're doing two extra queries for every row in the courses table, which is not very problematic if the courses table is not very large.

Stefan Gehrig Over a year ago

Looking at the subselect solution and the PHP solution it could be interesting which would perform better. Even though the PHP solution will have some network overhead as data has to be transmitted on the wire, the subselect soltion will suffer from the lack of the MySQL query cache for its subselects - although the complete query would be cached in the query cache.

Collectives™ on Stack Overflow

Mysql COUNT(*) on multiple tables

5 Answers 5

7 Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related