Join, Sum, Count the same database

az_wraith

I have a question of wheather I am doing the following correct, or is there a better way of doing the following.

$query = "SELECT e.*, l.*, l.id AS lid, 
	(SELECT sum(e.one) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid) AS t_one,
	(SELECT sum(e.two) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid) AS t_two,
	(SELECT sum(e.thr) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid) AS t_thr,
	(SELECT sum(e.fou) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid) AS t_fou,
	(SELECT count(e.one) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid AND e.one > '') AS c_one,
	(SELECT count(e.two) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid AND e.two > '') AS c_two,
	(SELECT count(e.thr) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid AND e.thr > '') AS c_thr,
	(SELECT count(e.fou) FROM events AS me JOIN layers l ON l.id = e.layer_id WHERE l.id = lid AND e.fou > '') AS c_fou
	FROM events e
	JOIN layers l ON l.id = e.layer_id
	ORDER BY e.event_id ASC";
$database->setQuery($query);
$layer = $database->loadObjectList();

nemonoman

I have never seen anything like this query. Can you help me understand what you're doing?

az_wraith

Ok, this will be a little hard to explain. What I have is 6 columns id, layer_id, one, two, thr, fou in events, and 2 columns in layer id, name,. What I am trying to do is SUM one, two, thr, and fou, and then also COUNT one, two, thr, fou if they are not NULL. In the end it spits out something like the total of one and then divide by by the count of one. This query works, I just need to know if there is an eaiser way of doing this.

laserlight

What I am trying to do is SUM one, two, thr, and fou, and then also COUNT one, two, thr, fou if they are not NULL.

It sounds like you should use another table.

Are the columns really named one, two, thr, and fou?

az_wraith

laserlight wrote:
Are the columns really named one, two, thr, and fou?

Yes for now

The problem i would run into is then I would have to redo the way I have it spitting the data out.

<?php 
	foreach ($layer as $value) { 
		$o_count = $value->c_one + $value->c_two + $value->c_thr + $value->c_fou;
		$o_sum = $value->t_one + $value->t_two + $value->t_thr + $value->t_fou;
		$o_hand = round($o_sum / $o_count * 4);
?>
	<tr>
		<td><?php echo $value->name; ?></td>
		<td><?php echo $value->p_id; ?></td>
		<td><?php echo $o_hand; ?></td>
		<td><?php echo $value->one; ?></td>
		<td><?php echo $value->two; ?></td>
		<td><?php echo $value->thr; ?></td>
		<td><?php echo $value->fou; ?></td>
	</tr>
<?php 
	} 
?>

laserlight

Yes for now

What are you trying to model? For example, why do you want to sum these columns?

nemonoman

Can you please show the output of this query?

az_wraith

Look at the post above I added the output of query. I jump the gun and hit submit then went back to edit at the same time you replied

laserlight

At the moment you have two tables:
events: {id, layer_id, one, two, thr, fou}
layer: {id, name}

From what I see, you have a many to many relationship that should be expressed with three tables, e.g.,
events: {id, value}
layers: {id, name}
events_layers {event_id, layer_id}

This way you can perform your SUM and COUNT by joining the tables.

az_wraith

laserlight wrote:
At the moment you have two tables:
events: {id, layer_id, one, two, thr, fou}
layer: {id, name}

From what I see, you have a many to many relationship that should be expressed with three tables, e.g.,
events: {id, value}
layers: {id, name}
events_layers {event_id, layer_id}

This way you can perform your SUM and COUNT by joining the tables.

laserlight, I don't understand. Are you saying create another table called events_layers? Why would I need to do that as all the data I need is in the two tables events, and layers.

laserlight

laserlight, I don't understand. Are you saying create another table called events_layers? Why would I need to do that as all the data I need is in the two tables events, and layers.

Because you have a many to many relationship between the two tables, as far as I can tell. That is the only feasible explanation for having such numbered columns.

You might want to read the PHPBuilder article on Database Normalization and Design Techniques.