Join 2 tables and sum with condition in codeigniter-CodePudding

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

CodePudding user response：

Use INNER JOIN and state = 1 as per given sample. Use MAX() for customer name to avoid string data type in GROUP BY clause. So use customer id at GROUP BY clause.

-- MySQL
SELECT MAX(c.name) name
     , SUM(s.price) price
FROM customers c
INNER JOIN sales s
        ON c.id = s.customerid
       AND s.state = 1
GROUP BY c.id

CodePudding user response：

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();