CODEWITHSUNDEEP
mysqldatabasecodeignitercodeigniter-3
Mumin Gazi
Mumin Gazi

Reputation: 103

Join 2 tables and sum with condition in codeigniter

I have 2 tables

Table1: customers:
-------------
| id | name |
-------------
| 1  | Mark |
-------------
| 2  | Tom |
-------------
| 3  | John |


Table2: sales:
-----------------------------------
|sid | customerid | price | state | 
-----------------------------------
| 10 | 1          | 12000 | 0     | 
-----------------------------------
| 11 | 2          | 13500 | 1     | 
-----------------------------------
| 12 | 2          | 23000 | 1     | 
-----------------------------------
| 13 | 3          | 26000 | 0     | 
-----------------------------------
| 14 | 1          | 66000 | 1     | 
-----------------------------------


the state column is 0=no dep  and 1=dept

I want to list the customers that have DEPT by checking them in the sales table. Now i'm looping the customers and checking them one by one. and it works! but when the number of rows in the customer table grows the page slows down. i want to make this by an SQL query. can anyone help me please ?

the result will be like this:

Mark  66000
Tom   36500

Upvotes: 0

Views: 455

Answers (2)

Coder India
Coder India

Reputation: 34

By the Following query, you will get the same output as you want. The joining of tables will be executed on the filtered data using where condition

$this->db->select('customers.name,sum(sales.price)')
->from('customers')
->join('sales','sales.customerid = customers.id','left')
->where('sales.state !=0')
->group_by('customers.name');
->get()->result_array();

Upvotes: 2

Eidikos Coder
Eidikos Coder

Reputation: 1

You can simply group by customer id in sales table. Code will be like this

return $this->db->select('MAX(customers.name) AS name, SUM(sales.price) as price')->join('sales', 'sales.customerid = customers.id')->where('sales.state', 1)->group_by('customers.id')->get('customers')->result();

Upvotes: 0

Related Questions