通过部门的总金额（包括公司）

Question

I am trying to run a SQL statement that includes three tables.

incomes

-id
-title
-amount
-company_id

companies

-id
-name
-sector_id

sectors

-id 
-name

as you can see 'incomes' table is not directly related with 'sectors' table. What I am trying to do is to get total of income by IndustrySectors.

I had managed to calculate two tables so far but knowledge is limited for three. Please kindly guide me.

this is for two tables.Income By Company

    $x=0;
    foreach($companies as $Company){
        $companyName= $Company['Company']['name'];
        $companyId= $Company['Company']['id'];

        $query= $this->Income->query("
        SELECT  SUM( amount ) AS IncomeTotal
        FROM incomes
        WHERE company_id=$companyId
        ");

        $total=$query[0][0]['IncomeTotal'];

        if($total!=null){
            //$incomeByCompany[$companyName]=$total;
            //$incomeByCompany['total']=$total;
            $incomeByCompany[$x]['companyId']=$companyId;
            $incomeByCompany[$x]['name']=$companyName;
            $incomeByCompany[$x]['total']=$total;

        }
        $x++;
    }

Answer 1

SELECT s.name , SUM(i.amount) as total_sector
FROM sectors s
JOIN companies c
   ON s.id = c.sector_id
JOIN incomes i
   ON c.id = i.company_id
GROUP BY s.name

Aditionally instead of a loop your company query should be:

SELECT c.name , SUM(i.amount) as total_sector
FROM companies c
   ON s.id = c.sector_id
JOIN incomes i
   ON c.id = i.company_id
GROUP BY c.name