-2

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];
user3783243
  • 5,368
  • 5
  • 22
  • 41
ar002
  • 9
  • 2
  • 2
    I assume you have a column storing timestamp in your table? Run `SHOW CREATE TABLE test_table;` and post the result into you question. Also, check MySQL version. – FanoFN Jan 15 '22 at 03:40
  • the answer is already in that `suggested by others`. Just fetch all the last 7 entries, and loop through to check and count `sleep` value. – zimorok Jan 15 '22 at 04:34

3 Answers3

0

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)

Gamer In The Game
  • 256
  • 1
  • 11
0

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
user3783243
  • 5,368
  • 5
  • 22
  • 41
0

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>";
Jerick Magnaye
  • 1