Get top selling customers

[eluser]webnology[/eluser]
Hi all,

I have a customers tabel and a revenue table. I would like to retrieve my top selling customers, so the ones with the highest sum of revenue in the revenues tables. I hav 2 fields per row for defining this revenue.

So ,it's like:

Revenue_id - rev_1 - rev_2
1 100€ 150€
2 50€ 65€
...

Can someone help me how to make a query selecting the top revenue rows where the sum of rev_1 and rev_2 adds up, joining this with the customers table.

Any help or direction very appreciated,
Michel

[eluser]Fabdrol[/eluser]
I'm not sure if you can add them up inside the SQL query, but I suppose you could join the customers table to the rev table and add them up in your code...
Think that's the best way, the performance shouldn't suffer too much I guess!

[eluser]webnology[/eluser]
Hey Fabdrol,

thx for helping. Got it working like this. No need to add it up in the code itself.

Code:

function getTopCustomers($user_id, $limit)
    {
        $this->db->select('*, SUM(treatment_price + arrangement_price) as revenue');
        $this->db->from('revenue');
        $this->db->join('customers', 'customers.customer_id = revenue.customer_id');
        $this->db->order_by('revenue', 'desc');
        $this->db->group_by('revenue.customer_id');
        $this->db->limit($limit);
        $this->db->where('revenue.user_id', $user_id, FALSE);
        $this->db->where('customers.flag_sleep', '0', FALSE);

        $Q = $this->db->get();
        return $Q;
    }

Cheers,
Michel

[eluser]Fabdrol[/eluser]
Hi Michel,

Looks great, personally I've never used join and stuff (I'm lazy enough to write two queries instead, and put them inside one big array)
What kind of output does this generate, (as you can see on a print_r() of the $Q->result() method)

Cheers,
Fabian

[eluser]gigas10[/eluser]
@Fabian, I used to do the technique of two queries, then I found join. Boy has my life been easier since I found join. Try it, it makes life much much simpler.