[thelist] MySQL COUNT() question

Mark Rees mrees at itsagoodprice.com
Tue Nov 15 02:47:10 CST 2005

>The new COUNT would be of the same type (branch,teacher,period) as fss, but
it would be from a different table >called f05lr_comments

You will  have to write a separate query to do that, I should think. Even if
it is somehow possible, it will almost certainly be easier to read and
maintain if you do. One other point, is student_num the identifier of a
student or an integer specifying the number of students on a course, say? If
the latter, you will probably want to use SUM rather than COUNT.

Aron Lurie wrote:
> Hey, I have a MySQL problem that is posing a severe challenge to me
regarding COUNT().
> Right now I have the following code:
> select CONCAT(fpe.fname,' ',fpe.lname) fullname,
>  pe.alias,
>  fce.Course,
>  COUNT(*) student_num
> from f05people fpe,
>  f05classes fce,
>  people pe,
>  f05studentsched fss
> where pe.hcid=fpe.hcid
>  and pe.alias=fce.Faculty
>  and fpe.Faculty='Yes'
>  and fce.Branch='HC-Tuesday'
>  and fce.period='1'
>  and fss.period='1'
>  and fss.branch='HC-Tuesday'
>  and fss.teacher = pe.alias
>  group by fss.branch, fss.teacher,fss.period order by fce.Faculty ASC;
> This works fine so far returning all the values I want, however...
> The problem is that I want another COUNT(*) column that is grouped by a
different set of fields in a different table. Is there a way to have two
group by statements? How would I do this?

Matt Warden:
Not like that, no.

What count do you want? If it is truly separate from this query, you
need another query. However, if it is a field in this query, we can
probably come up with a solution for you.

More information about the thelist mailing list