A join and a SUM()

[eluser]webnology[/eluser]
Hi all,

I have a table with arrangements, and a table which keeps the revenue per arrangement. I'm trying to show a table of all arrangements with their total revenue. I have this code:

Code:

$this->db->select('arrangements.*, SUM(revenue.arrangement_price) as revenue');
        $this->db->from('arrangements');
        $this->db->join('revenue', 'revenue.arrangement_id = arrangements.arrangement_id', 'inner');
        $this->db->order_by('arrangements.title', 'asc');
        $this->db->where('arrangements.user_id', $user_id, FALSE);
        
        $Q = $this->db->get();
        return $Q;

But I'm only getting 1 arrangement, where I should have about 10. And the revenue is the total revenue of all arrangements, not for the specific arrangement.

Can someone help me to fix this, please?

Kind regards,
Michel

[eluser]momo123[/eluser]
Be happy that you get a result ;-) Every other database than MYSQL gives an error if you try above.
You have to group your columns with the group-by-function. Read this: http://dev.mysql.com/doc/refman/5.0/en/g...tions.html

Good luck!

[eluser]webnology[/eluser]
Thx momo, that indeed did it!