Help with simple select

[kaylee]

Hi everyone,

 

I am new to mysql and databases in general.

 

I have an "attendance" table with the following information:

date, school, class, and studentid.  I would like to list the number of

students for each class, for each school, for each day even if the attendance was zero.

 

If I had 2 schools(s1, s2) and 3 classes (c1,c2,c3), what is the best way to get an output such as:

 

--------------------------------

day - school - class - students

--------------------------------

1 - s1 - c1 - 5

1 - s1 - c2 - 0

1 - s1 - c3 - 1

1 - s2 - c1 - 1

1 - s2 - c2 - 2

1 - s2 - c3 - 0

 

2 - s1 - c1 - 0

2 - s1 - c2 - 3

2 - s1 - c3 - 4

2 - s2 - c1 - 20

2 - s2 - c2 - 23

2 - s2 - c3 - 31

 

3 - s1 - c1 - 9

3 - s1 - c2 - 2

3 - s1 - c3 - 3

3 - s2 - c1 - 0

3 - s2 - c2 - 32

3 - s2 - c3 - 23

..

..

..

 

I tried "SELECT datetime, school, class, COUNT(studentid) as students FROM attendance GROUP BY datetime, school, class ORDER BY datetime, school, class;"

 

But this will not return rows with empty student count

 

--------------------------------

day - school - class - students

--------------------------------

1 - s1 - c1 - 5

1 - s1 - c3 - 1

1 - s2 - c1 - 1

1 - s2 - c2 - 2

 

 

2 - s1 - c2 - 3

2 - s1 - c3 - 4

2 - s2 - c1 - 20

2 - s2 - c2 - 23

2 - s2 - c3 - 31

 

3 - s1 - c1 - 9

3 - s1 - c2 - 2

3 - s1 - c3 - 3

3 - s2 - c2 - 32

3 - s2 - c3 - 23

 

 

should I be looking into JOINS here?

Thanks in advance,

K

[amites]

I'm sure theres a better way to do this

 

though my first impression would be a couple nested for loops

 

<?php
$schools = [number of schools];
$days = [number of days];
$class = [number of classes];

for ($d=0; $d <= $days; $d++) {

for ($s=0; $s <= $schools; $s++) {

	for ($c=0; $c <= $class; $c++) {

		$query = "SELECT count(studentid) AS students FROM attendance
			WHERE day = " .$d. "
			AND school = " .$s. "
			AND class = " .$c;
		$result = mysql_query($query);

		print $d . " - s" . $s . " - c" . $c . " - " . mysql_result($result, 0, 0) . "<br />";

	}

}

}

?>

 

syntax might be off as I typed this in the browser but gives one approach that will work

[kaylee]

Thanks for the reply.

 

The "day" is a datetime variable. Also, the classes and schools can be more than 3.

 

 

[amites]

that's why I built the query with variables, you can change the number of schools, classes etc...

 

for the date same principal, have to build the timestamp inside the for loop

 

ex: YYYY-MM-$d 01:00:00

 

something to that effect

[kaylee]

hmm, is there way to get the results with MySql alone? ie. without using php or outside scripts?

[revraz]

Have you thought about having 0 as the default instead of NULL?

 

A 0 should return.

[fenway]

Your original post didn't join any tables... so it shouldn't miss any counts.  With joins, you'll need LEFT JOIN.

[kaylee]

Thanks, LEFT join on the same table is what I was looking for.

I will figure this out and post a solution.