codeigniter sum query for single column [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 3 months ago.

Improve this 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)

i am not familiar with coedigniter, can you post regular SQL — radar
– radar, Commented Sep 29, 2014 at 15:55
Not working is vague, what do you expect and what do you get? Also you're talking about sum but you return the number of rows — Gervs
– Gervs, Commented Sep 30, 2014 at 8:30

Sociopath · Accepted Answer · 2018-09-07 07:24:44Z

1

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;
    }

edited Sep 7, 2018 at 7:24

Sociopath

13.4k22 gold badges53 silver badges82 bronze badges

answered Sep 7, 2018 at 7:04

Malek

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vertig0 · Accepted Answer · 2018-06-28 21:32:57Z

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:

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.
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

Collectives™ on Stack Overflow

codeigniter sum query for single column [closed]

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related