mysql query count fields

Question

I've got some trouble with a sql query if someone can help me will be brilliant. So I have students, participation, courses, places, student_to_courses these tables. I need to get all students in particular course and I'm doing with this query

select 
  s.name as student_name, c.name as course_name, p.name as place_name
from 
  student_to_courses s2c, 
  students_participation sp, 
  students s,
  courses c,
  places p

where 
s.id = s2c.participation_id AND
s.id = sp.profile_id AND 
c.id = s2c.course_id AND
p.id = c.place_id = p.id AND
s2c.course_id = 1

Here is sqlfiddle

but I need to count how many times the student has been in particular place, something like this student_name, course_name, place_name, | 3 |. Now I'm doing it with another query which I call each time in my loop. But I was wondering if there is a way to do it just with single query. Thanks in advance to everyone who would like to help me.

Answer 1

You can just do a COUNT() and then GROUP BY the remaining columns:

select s.name as student_name, 
       c.name as course_name, 
       p.name as place_name, 
       count(*)
from students s
inner join student_to_courses s2c on s.id = s2c.participation_id
inner join courses c on c.id = s2c.course_id
inner join places p on p.id = c.place_id
inner join students_participation sp on s.id = sp.profile_id
where s2c.course_id = 1
group by s.name,c.name,p.name 

I also changed your joins to explicit joins, since it is much easier to read an less error prone.

sqlfiddle demo

Answer 2

Try this:

select 
  s.name as student_name, c.name as course_name, 
  p.name as place_name, count(*) qtd
from 
  student_to_courses s2c, 
  students_participation sp, 
  students s,
  courses c,
  places p
where 
   s.id = s2c.participation_id AND
   s.id = sp.profile_id AND 
   c.id = s2c.course_id AND
   p.id = c.place_id = p.id AND
   s2c.course_id = 1
group by s.name, c.name, p.name

The count(*) is an AGGREGATE FUNCTION See this link to understand.