MySQL INNER JOIN Of 3 Tables With Count And Totals

I have the following sample database set up - CREATE TABLE IF NOT EXISTS `companies`( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `company` varchar(75) NOT NULL, PRIMARY

Solution 1:

Since the orders are linked to the companies via the customers, I don't think you need to perform two separate subqueries and join them; rather, I think you can just write:

SELECT companies.company AS "Company",
       IFNULL(COUNT(DISTINCT companies_customers.customer_id), 0) AS "Total Customers",
       IFNULL(SUM(orders.amount), 0) AS "All Orders Total"
  FROM companies
  LEFT
  JOIN companies_customers
    ON companies_customers.company_id = companies.id
  LEFT
  JOIN orders
    ON orders.customer_id = companies_customers.customer_id
 GROUP
    BY companies.id
;

Edited to add: That said, I have to say that the schema doesn't really make sense to me. You have a many-to-many relationship between customers and companies — so, for example, John Smith is a customer of Acme Widget Company and of Intrepid Inc. — but then orders are just a property of the customer, not of the company. This means that if an order belongs to John Smith, then it necessarily belongs both to Acme Widget Company and to Intrepid Inc.. I don't think that can be right. Instead of having a customer_id field, I think orders needs to have a companies_customers_id field.

Solution 2:

I have 3 table that to keep team,tournament_round AND score_team_member about competition TEAM_A Vs TEAM_B ,multiple of round.(1,2,3...n) and multiple of members of team there is score by oneself.This code above are useful very much.

SELECT team.name AS "TEAM",team.id,
       IFNULL(COUNT(DISTINCT `tournament_round`.id), 0) AS "TotalWin",
       IFNULL(SUM(`score_team_member`.`score`)/(select count(*) from `team_member`where team_id=team.id group by team_id ), 0) AS "ScoreofTeam"
  FROM `team`
  LEFT
  JOIN `tournament_round`
    ON `tournament_round`.team_winner_id = `team`.id 
  LEFT
  JOIN `score_team_member`
    ON `score_team_member`.team_id = `team`.id
WHERE `team`.thematch_id='6' AND `team`.`category1`='MEP'
 GROUP    BY `team`.id ORDER by `TotalWin`DESC ,`ScoreofTeam` DESC

Sample out put click here JPG
Table Designer JPG