MySQL SUM with multiple GROUP BY on same table

Question

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円');

• Mihai: You can use CASE WHEN... THEN...
• Yamada: I'm starting to believe that this kind of select is best handled in a procedural code or split in several independent selects that are called from client application.

Answer 1

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!

• ypercubeᵀᴹ: If you remove the brackets [ and ], the code will work in MySQL just fine. And it's a CASE expression, not statement. (this is not just a trivia, MySQL has a CASE statement as well which is a different thing and can be used in stored procedures.)
• Glen Swan: Roger! Edit's added!
• Yamada: Great Glen Swan! The complexity of your query is very low compared to my original select. When I got a comment on my question from Mihai I found the statement spec only, I didn't realize there was an expression for this purpose. Thanks also for this disambiguation from @ypercube. Using this approach will deliver better performance compared to handling this on the client application!
• Glen Swan: No problems! Sometimes simple is the best way to go.