codeigniter sum query for single column

Question

Have a query that I'm trying to return the sum result of the columns where the value is over 10,000. I've tried adding sum(value) as total to the query but it's not working.

My code is:

    $this->db->select('sum(value) as total, date, member_id');
    $this->db->where('member_id', $_SESSION['sess_user_id']);
    $this->db->where('cost.value >', '9999');
    $this->db->where('cost.date = DATE_SUB(NOW(), INTERVAL 5 DAY)');

    $query = $this->db->get('orders');

    return $query->num_rows();

here's the raw SQL output:

SELECT sum(value) as total, date, member_id FROM (steps) WHERE member_id = '1' AND cost.value > '9999' AND cost.date = DATE_SUB(NOW(), INTERVAL 5 DAY)

Answer 1

Is not clear what do you want, but:

If you need the sum of the values for a specific user where the date is 5 days ago, your query should work, but there are 2 problematic things:

1. This is probably a typo, but in your code, you get the data from "orders" and in your raw query, the data is got from "steps". If it is not a typo, you are looking in the wrong place.
2. In your "where", you are asking for rows where "date" is equal (=) to 5 days ago, but using "now()" you are asking for rows with date exactly 5 days ago, this is, including hour, minute and second.

If you need the number of rows where the value is 10000 or more of a specific user, you must not use "SUM" or you always gonna get 1 as result (or null). Besides that, again, the date is exactly 5 days ago, including hours, minutes and seconds.

On the other hand, you say you want the number of columns, i think you need the number of rows, but if you really need the number of columns, you need to change

SELECT sum(value) as total, date, member_id 

to

SELECT *

and

return $query->num_rows();

to

return $query->num_fields();

Hope it helps