I want to count records from a database where the columns have the same value

Question

I have t table which contains views from books:

ID    UserID    TIME            BookID
1     2         (timedate)      1
1     3         (timedate)      1
1     54        (timedate)      1
1     4        (timedate)       2
1     5        (timedate)       2
much more ....

I been trying to get a query which counts the number of views of the books and list them from highest to lowest.

I want to do this so I can display 5 books with the highs amount of views in the past month.

Non of the given are working!

Im putting it through a while loop if that helps.

$gethotbookq = mysqli_query($con,"select BookID, count(*) as views
from `BOOKS_views`
where `TIME` >= '2014-05-01 00:00:00' and `TIME` < '2014-06-01 00:00:00'
group by BookID
order by views desc
limit 5") or die();

while($gethotbook = mysql_fetch_array($gethotbookq))
{
$bookid = $gethotbook['BookID'];

}

sorry I have lost some of the formatting – Kyle Somerset May 25 '14 at 17:22 — Kyle Somerset, May 25 '14 at 17:22

score 0 · Accepted Answer · answered May 25 '14 at 17:25

0

select bookID, count(*) as views
from books
where `time` >= '2014-04-01' and `time` < '2014-05-01'
group by bookID
order by views desc
limit 5

answered May 25 '14 at 17:25

juergen d

201,996
37
293
362

didn't work pal, I've updated the question with my code – Kyle Somerset May 25 '14 at 17:58
What does *didn't work* mean exactly? Did you get an error? Did you get unexpected results? How does your dates look like? What data type are they? – juergen d May 25 '14 at 18:12
This answer is missing its educational explanation. – mickmackusa Apr 20 '21 at 09:52

score 0 · Answer 2 · edited May 23 '17 at 11:57

Are the UserIDs the views?

this may help with counting the number of times a book shows up in the book ID. As long as there are not a lot of the smae user looking at the same book then the views should be pretty accruate.

SQL: How to get the count of each distinct value in a column?

following that you should have a list of all the booksIDs and a count of views and could sort the views highest to lowest.

I want to count records from a database where the columns have the same value

2 Answers2