Trying to count all records from database |
Hi,
I am trying to grab the last 30 days of a table and using the pageid group them together but also add the groups together. I have the following which does group them, but doesn't add them together. I am not sure what I am missing. PHP Code: $query = $this->db->query('SELECT *, COUNT(*)FROM analytics_page_views WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW() GROUP BY pageid'); Can anyone advise on how I can get the group to count them as well please? Thanks, Doomie
Sorry that is not quite what I want.
What I am trying to do is to group together all the last 30 days with the same pageid and then with each of the groups add just them together. What you have given me is to add everything together into one row and then it breaks the page.
$query = $this->db->query('SELECT *, COUNT(pageid) as total_count
FROM analytics_page_views WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW() GROUP BY pageid',array()); return $query->result(); You should add alias after count() since you use (*) to not conflict to other fields
I think I am almost there with this.
I currently have the following in the database: page_hits | date | pageid 5 | 2017-09-14 | testing 5 | 2017-09-01 | testing and on the page I am wanting it to have pageid | page_hits testing | 10 But instead I am getting: pageid | page_hits testing | 2 Here is how far I have got my code to: PHP Code: $date = new DateTime('FIRST DAY OF 0 MONTHS'); So i know that its using the page_hits as how many rows their is but not adding the values in page_hits together.
You have grouped it by pageid. Since both your pageid's are the same they are grouped as one row. There are two of them.
https://www.w3schools.com/sql/sql_groupby.asp Also in the docs, group_by is near the bottom of this section: https://www.codeigniter.com/user_guide/d...milar-data |
Welcome Guest, Not a member yet? Register Sign In |