MySQL SUM with multiple GROUP BY on same table

MySQLperformancequery-performance

What is the best way to produce several sum results based on different group by conditions on the same table in MySQL?

I believe I'm doing a bad select, but I can't figure out a better way.

Imagine a have a orders table where I track the client that made the order, it's value and some qualifiers for the order (the types). I want to sum the total orders for a given client grouped by a combination of the order types:

delimiter $$

CREATE TABLE `orders` (
  `idorder` int(11) NOT NULL,
  `value` double DEFAULT NULL,
  `idclient` int(11) DEFAULT NULL,
  `type1` int(11) DEFAULT NULL,
  `type2` int(11) DEFAULT NULL,
  `type3` bit(1) DEFAULT NULL,
  PRIMARY KEY (`idorder`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

My select:

SELECT T0.idclient, T1.condition1, T2.condition2, T3.condition3 FROM
(SELECT 1 AS idclient) AS T0
LEFT JOIN
(SELECT idclient, SUM(value) condition1 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 0) AS T1
ON T0.idclient = T1.idclient
LEFT JOIN
(SELECT idclient, SUM(value) condition2 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 1) AS T2
ON T0.idclient = T2.idclient
LEFT JOIN
(SELECT idclient, SUM(value) condition3 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 0 AND type3 = 0) AS T3
ON T0.idclient = T3.idclient;

Am I missing something?

Output:

+----------+------------+------------+------------+
| idclient | condition1 | condition2 | condition3 |
+----------+------------+------------+------------+
|        1 |         10 |         20 |         30 |
+----------+------------+------------+------------+
1 row in set (0.00 sec)

Data:

INSERT INTO `orders` VALUES (1,5,1,1,1,'\0'),(2,5,1,1,1,'\0'),(3,20,1,1,1,''),(4,5,1,1,0,'\0'),(5,25,1,1,0,'\0');

Best Answer

I'm more of a SQL Server guy, but here is a stab to get you motivated. You could use the CASE expression to help SUM() orders by certain order types.

For example:

SELECT
    idclient AS [Client ID]
   ,COUNT(value) AS [Total Orders]
   ,SUM(value) AS [Total Order Value]
   ,SUM( CASE WHEN type1 = 'some type' THEN value ELSE 0 END) AS [Order Combo 1]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' THEN value ELSE 0 END) [Order Combo 2]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' AND type3 = 'another type' THEN value ELSE 0 END) [Order Combo 3]
FROM 'test'.'orders'
GROUP BY idclient

This will group the orders on [idclient] and will SUM([value]) the instances where certain order records meet the following conditions or combinations you specified.

So, if a customer has an order where [type1] was Apples and [type2] was Oranges, you can SUM([value]) that was 1 under a new column called something like [Apple/Orange Orders].

The result can also include additional combinations for the same record in multiple fields/columns. If that is a problem, just move the CASE to the GROUP BY to break each combination into a separate record in the the result set.

Hope this helps!

Related Question