CODEWITHSUNDEEP
mysqlsqlcodeigniter-2
jamper
jamper

Reputation: 277

codeigniter sum query for single column

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)

Upvotes: 2

Views: 15595

Answers (2)

Malek
Malek

Reputation: 11

function cart_product_join()
    {
        $this->db->select('products.product_id,cart.product_id,SUM(products.special_price*cart.product_qty)');
        $this->db->from('products');
        $this->db->join('cart', 'cart.product_id = products.product_id', 'right');
        $result = $this->db->get()->result();
        return $result;
        //$result=$this->db->last_query();
        //echo $result;
    }

Upvotes: 1

Vertig0
Vertig0

Reputation: 623

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

Upvotes: 0

Related Questions