Jump to content

Limit Count


az_wraith

Recommended Posts

I am tring to Limit the count to the last 50 entries. I can not figure out how to add that to my SQL query. Here is the query.

SELECT e.*, l.*,	(SELECT count(one) FROM events WHERE layer_id = l.id AND one > '') AS c_one,	(SELECT count(two) FROM events WHERE layer_id = l.id AND two > '') AS c_two,	(SELECT count(thr) FROM events WHERE layer_id = l.id AND thr > '') AS c_thr,	(SELECT count(fou) FROM events WHERE layer_id = l.id AND fou > '') AS c_fou,	(SELECT sum(one) FROM events WHERE layer_id = l.id) AS t_one,	(SELECT sum(two) FROM events WHERE layer_id = l.id) AS t_two,	(SELECT sum(thr) FROM events WHERE layer_id = l.id) AS t_thr,	(SELECT sum(fou) FROM events WHERE layer_id = l.id) AS t_fou	FROM events AS e	JOIN layers l ON l.id = e.layer_id	WHERE e.match_id = $match->match_id	ORDER BY e.event_id ASC

Link to comment
Share on other sites

If it was that easy I would not be asking, but I am not tring to limit the database results but the Count and Sum to the last 50 entrys inserted.

(SELECT count(one) FROM events WHERE layer_id = l.id AND one > '') AS c_one(SELECT sum(one) FROM events WHERE layer_id = l.id) AS t_one

Link to comment
Share on other sites

I thought the same, I also thought this one would be easy. From searching the web, I have found that MySQL had a bug in MySQL 4.1 with the Limit on subqueries, but that was from 2006 and I would think that should be fixed by now. I am currently updating MySQL on server to 5 and I will go from there.

Link to comment
Share on other sites

Have you tried running these on something like phpMyAdmin? Run a few queries and see if you can find out where it stops working.SELECT count(one) FROM events WHERE layer_id = layers.id AND one > ''SELECT count(one) FROM events WHERE layer_id = layers.id AND one > '' LIMIT 50SELECT '1' AS one, (SELECT count(one) FROM events WHERE layer_id = layers.id AND one > '') AS c_oneSELECT '1' AS one, (SELECT count(one) FROM events WHERE layer_id = layers.id AND one > '' LIMIT 50) AS c_oneOr for testing it might be better to use a smaller limit so you make sure the limit is coming into play, I'm not sure what data you're working with.You'll also want to change this:WHERE e.match_id = $match->match_idto this:WHERE e.match_id = {$match->match_id}

Link to comment
Share on other sites

justsomeguy,I did as you suggested in phpMyAdmin. The weird thing isboth the following returned 241 results, even with the limit 50 on the second one.SELECT count(one) FROM events WHERE layer_id = layers.id AND one > ''SELECT count(one) FROM events WHERE layer_id = layers.id AND one > '' LIMIT 50both of these returned 1 one, and 241 results on count(one)SELECT '1' AS one, (SELECT count(one) FROM events WHERE layer_id = layers.id AND one > '') AS c_oneSELECT '1' AS one, (SELECT count(one) FROM events WHERE layer_id = layers.id AND one > '' LIMIT 50) AS c_oneAlso thanks for your help on this one, as I am stumped.

Link to comment
Share on other sites

OK, we're thinking about it the wrong way. It's only returning one row, which happens to be the count. It's not returning more then 50 rows where it would be limited, it's only one row that contains one count. Try this:SELECT count(SELECT one FROM events WHERE layer_id = layers.id AND one > '' LIMIT 50) AS c_oneWe're getting into a lot of subqueries, but it might be the only way. So that might make the original something like this:

SELECT e.*, l.*,	SELECT count(SELECT one FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_one,	SELECT count(SELECT two FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_two,	SELECT count(SELECT thr FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_thr,	SELECT count(SELECT fou FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_fou,	SELECT sum(SELECT one FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS t_one,	...

Link to comment
Share on other sites

Ok I have tried that and I am getting an error. I have slim lined the query to 1 count for now just to get it working. here is what I have and getting an error.

SELECT me.*, l.*,	SELECT count(SELECT one FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_one	FROM events AS e	JOIN layers l ON l.id = e.layer_id	WHERE e.match_id = {$match->match_id}	ORDER BY e.event_id ASCYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'SELECT count(SELECT one FROM events WHERE layer_id = l.id\' at line 2 SQL=SELECT e.*, l.*, \n SELECT count(SELECT one FROM events WHERE layer_id = l.id AND one > \'\' LIMIT 50) AS c_one \n FROM events e \n JOIN layers l ON l.id = e.layer_id \n WHERE e.match_id = 48 \n ORDER BY e.event_id ASC

Link to comment
Share on other sites

Remove that second SELECT, you don't need it there.

SELECT me.*, l.*,	count(SELECT one FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_one	FROM events AS e	JOIN layers l ON l.id = e.layer_id	WHERE e.match_id = {$match->match_id}	ORDER BY e.event_id ASC

Link to comment
Share on other sites

Remove that second SELECT, you don't need it there.
SELECT me.*, l.*,	count(SELECT one FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_one	FROM events AS e	JOIN layers l ON l.id = e.layer_id	WHERE e.match_id = {$match->match_id}	ORDER BY e.event_id ASC

Still no go. getting the same error.
Link to comment
Share on other sites

here is the error

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 \'SELECT one FROM events WHERE layer_id = l.id AND one > \'\'\' at line 2 SQL=SELECT e.*, l.*, \n count(SELECT one FROM events WHERE layer_id = l.id AND one > \'\' LIMIT 50) AS c_one \n FROM events e \n JOIN layers l ON l.id = e.player_id \n WHERE e.match_id = 48 \n ORDER BY e.event_id ASC

Link to comment
Share on other sites

I'm trying to find information about this in the manual. It's complaining about the SELECT statement inside the COUNT function. Here is the section in the manual about the COUNT aggregate function:http://dev.mysql.com/doc/refman/5.0/en/gro...#function_countThe definition is pretty simple:

COUNT(expr) Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
The problem is, I can't find a definition for an "expression" in the MySQL manual. I'm sure there has to be one, but I can't find it, so I'm not sure if a SELECT statement is considered to be a valid expression or not. I'll keep looking, but if you can figure out where in the manual it says what is a valid expression, which is what COUNT expects, that might help.
Link to comment
Share on other sites

justsomeguy,This is what I have done to get rid of the SELECT error, but now I am getting a different error, and maybe on to something.

SELECT me.*, l.*,	count((SELECT one FROM events WHERE layer_id = l.id AND one > '' LIMIT 50)) AS c_one	FROM events AS e	JOIN layers l ON l.id = e.layer_id	WHERE e.match_id = {$match->match_id}	GROUP BY e.event_id ORDER BY e.event_id ASC

The error I am getting now is

Subquery returns more than 1 row SQL=SELECT e.*, l.*, \n \n count((SELECT one FROM events WHERE layer_id = l.id AND one > \'\' LIMIT 50)) \n \n FROM events e \n JOIN layers l ON l.id = e.layer_id \n WHERE e.match_id = 48 \n GROUP BY e.event_id ORDER BY e.event_id ASC

Link to comment
Share on other sites

It's related to the same thing. I don't think you can use a select query as an expression for an aggregate function like count. Aggregate functions are typically used with GROUP BY and act on the results that were returned from the query they are in, rather then counting the results in another query. I'm not sure if this is something you can do in SQL without using something like a stored procedure. If you don't want to use a stored procedure then you might just need to get all of the records and do the counting and sums in PHP.

Link to comment
Share on other sites

Ok, maybe I have been looking into this too long and getting flustrated, or I have just lost some memory in my head. I can't wrap my head around what you said by "Just set up some variables that start at 0 and increment one for the count and add values to the other for the sum.". This is what I have, also I have changed the SQL query a little.I am still tring to limit layer_count and layer_sum to the last 50 database entries. justsomeguy, I also want to tell you how much I appreciate your help.

$query = "SELECT e.*, l.*, l.id AS lid,		(SELECT count(one) FROM events WHERE layer_id = l.id AND one > '') +		(SELECT count(two) FROM events WHERE layer_id = l.id AND two > '') +		(SELECT count(thr) FROM events WHERE layer_id = l.id AND thr > '') +		(SELECT count(fou) FROM events WHERE layer_id = l.id AND fou > '') AS layer_count,		(SELECT sum(one + two + thr + fou) FROM events WHERE layer_id = l.id) AS layer_sum	FROM events e, layers l	WHERE e.match_id = {$match->match_id} AND lid = e.layer_id	ORDER BY e.event_id ASC";$database->setQuery($query);$layer = $database->loadObjectList();<?php 	$i = 0;	foreach ($layer as $key => $value) { 	$layer_sum = $value->layer_sum;	$layer_count = $value->layer_count;	$layer_layer = round($layer_sum / $layer_count * 4);?>	<tr width="100%">		<td>  <?php echo $value->name." ".$layer_layer." ".$layer_sum." ".$layer_count; ?></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 	$i++;	} ?>

Link to comment
Share on other sites

I can't wrap my head around what you said by "Just set up some variables that start at 0 and increment one for the count and add values to the other for the sum."
It's nothing difficult. For a count, have a variable start at 0 and add 1 to it every time through the loop. A sum is the total of a bunch of numbers, so have another variable start off at 0 and add whatever the number is to it in the loop.
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...