MySQL INNER JOIN of 3 tables with count and totals

Question

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 KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `companies` (`id`, `company`) VALUES
(1, 'Acme Widget Company'),
(2, 'Intrepid Inc.'),
(3, 'Allied Corp.');

CREATE TABLE IF NOT EXISTS `companies_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `companies_customers` (`id`, `company_id`, `customer_id`) VALUES
(1, 2, 1),
(2, 2, 2),
(3, 2, 4),
(4, 1, 3),
(5, 1, 1);

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `customers` (`id`, `firstname`, `lastname`)   VALUES
(1, 'John', 'Smith'),
(2, 'Sue', 'Jones'),
(3, 'David', 'Flanders'),
(4, 'Kathy', 'Freeman');

CREATE TABLE IF NOT EXISTS `orders`  (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `amount` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `orders` (`id`, `customer_id`, `amount`) VALUES
(1, 1, 500),
(2, 3, 1000),
(3, 1, 250),
(4, 4, 800),
(5, 4, 100);

I need to write a query which retrieves a list of all company names, a count of the number of customers in each company, and a sum of the customers orders in each company, like this -

Company                 Total Customers All Orders Total
Acme Widget Company     2               750
Intrepid Inc.           3               1650
Allied Corp.            0               0

I nearly have it resolved with the following SQL -

SELECT company AS 'Company', customersCount AS 'Total Customers', customerOrdersTotal AS 'All Orders Total'
  FROM
  ( SELECT cc.customer_id, SUM(innerQuery.ordersTotal) customerOrdersTotal
    FROM (SELECT cu.id customerId, SUM(amount) ordersTotal
          FROM customers cu
          JOIN orders o ON o.customer_id = cu.id
          GROUP BY customerId
         ) innerQuery
    JOIN companies_customers cc ON innerQuery.customerId = cc.customer_id
    GROUP BY cc.customer_id
  ) inner_1
  RIGHT JOIN 
    ( SELECT cc.id, c.company, COUNT(*) customersCount
      FROM companies c
      JOIN companies_customers cc ON c.id = cc.company_id
      GROUP BY c.id
    ) inner_2
  ON inner_1.customer_id = inner_2.id

It does not print out the company (Allied) without a customer or total. So close, I just need a nudge in the right direction. Thanks.

ruakh ruakh · Accepted Answer · 2012-03-19T15:13:57

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.

MySQL INNER JOIN of 3 tables with count and totals

2 Answers