How to count contents of last n rows mysql-CodePudding

I am trying to figure out how to see how many times in the past 7 entries/rows that sleep = 1.

Currently, $num shows the number of times sleep = 1 in all rows. I have seen that 'order by xxx desc limit 7' has been suggested in other answers but it doesn't seem to work well in this scenario. Would greatly appreciate any help, thanks!

Heres my code:

$result = mysqli_query($conn, "SELECT count(*) FROM test_table WHERE sleep = 1");    
$row = mysqli_fetch_row($result);
$num = $row[0];

CodePudding user response：

You can try this one:

SELECT a, COUNT(b) FROM test_table
WHERE sleep = 1
GROUP BY a
ORDER BY COUNT(b) ASC
LIMIT 7

Here, a is the name of your column you are trying to count
And, b is any column name for usage to count (it can id, or any column name)

CodePudding user response：

If the sleep is binary/tinyint you can just sum that in the query with the order by.

SELECT sum(sleep)
FROM table
ORDER BY COUNT(id) DESC
LIMIT 7

If sleep isn't binary you can use a case statement.

SELECT sum(case when sleep = 1 then 1 else 0 end) as totalsleep
FROM table
ORDER BY COUNT(id) DESC
LIMIT 7

CodePudding user response：

Here's my idea, get all the data in test_table and create a loop that will count the sleep, like this

$result = mysqli_query($link, "SELECT sleep FROM test_table;");
$x=1;
$sleep = [];
$SleepCount= 0;
while($row = mysqli_fetch_array($result)) {

  if($row[0] == "1"){
    $SleepCount  ;
  }
  if($x == 7){
     array_push($sleep,$SleepCount);
     $SleepCount = 0;
     $x=0;
  }
   $x  ;
}

echo "<pre>",print_r($sleep),"</pre>";