az_wraith Posted March 8, 2008 Share Posted March 8, 2008 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 More sharing options...
zppblood Posted March 8, 2008 Share Posted March 8, 2008 "... ORDER BY e.event_id DESC LIMIT 50" Link to comment Share on other sites More sharing options...
az_wraith Posted March 8, 2008 Author Share Posted March 8, 2008 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 More sharing options...
justsomeguy Posted March 8, 2008 Share Posted March 8, 2008 It's the same thing.(SELECT count(one) FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_oneetc Link to comment Share on other sites More sharing options...
az_wraith Posted March 10, 2008 Author Share Posted March 10, 2008 It's the same thing.(SELECT count(one) FROM events WHERE layer_id = l.id AND one > '' LIMIT 50) AS c_oneetcThat does not work either. That what I have done but still pull all results back. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 10, 2008 Share Posted March 10, 2008 That doesn't make sense, if there's a limit on the SELECT statement then it should not get all results unless the number of results are less then the limit number. Link to comment Share on other sites More sharing options...
az_wraith Posted March 10, 2008 Author Share Posted March 10, 2008 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 More sharing options...
az_wraith Posted March 11, 2008 Author Share Posted March 11, 2008 well MySQL 5.0.45 also does the same thing. There must be something wrong with the query itself. I searched the web for Limit in subqueries but no positive answer for a solution. If anyone has any ideas could you please let me know. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 11, 2008 Share Posted March 11, 2008 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 More sharing options...
az_wraith Posted March 11, 2008 Author Share Posted March 11, 2008 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 More sharing options...
justsomeguy Posted March 11, 2008 Share Posted March 11, 2008 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 More sharing options...
az_wraith Posted March 11, 2008 Author Share Posted March 11, 2008 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 More sharing options...
justsomeguy Posted March 11, 2008 Share Posted March 11, 2008 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 More sharing options...
az_wraith Posted March 11, 2008 Author Share Posted March 11, 2008 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 More sharing options...
justsomeguy Posted March 11, 2008 Share Posted March 11, 2008 I removed the part that was causing that error, the error should be different now. Is the error now referencing the inner SELECT statement? Link to comment Share on other sites More sharing options...
az_wraith Posted March 12, 2008 Author Share Posted March 12, 2008 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 More sharing options...
justsomeguy Posted March 12, 2008 Share Posted March 12, 2008 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 More sharing options...
az_wraith Posted March 12, 2008 Author Share Posted March 12, 2008 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 More sharing options...
justsomeguy Posted March 12, 2008 Share Posted March 12, 2008 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 More sharing options...
az_wraith Posted March 19, 2008 Author Share Posted March 19, 2008 justsomeguy,I am now just getting time to get back to this. In php how would I go about doing the counting and sum? Should this question be asked in the PHP forum section or should this stay here as it pertains to SQL also? Link to comment Share on other sites More sharing options...
justsomeguy Posted March 19, 2008 Share Posted March 19, 2008 Either way. Just set up some variables that start at 0 and increment one for the count and add values to the other for the sum. There's nothing tricky about it. Link to comment Share on other sites More sharing options...
az_wraith Posted March 22, 2008 Author Share Posted March 22, 2008 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 More sharing options...
justsomeguy Posted March 25, 2008 Share Posted March 25, 2008 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.