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`)

My select:

SELECT T0.idclient, T1.condition1, T2.condition2, T3.condition3 FROM
(SELECT 1 AS idclient) AS T0
(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
(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
(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?


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


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');
  • You can use CASE WHEN... THEN...
    – Mihai
    Sep 22, 2014 at 17:18
  • 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.
    – Yamada
    Sep 22, 2014 at 19:40

1 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:

    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!

  • 1
    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.) Sep 24, 2014 at 6:30
  • Roger! Edit's added!
    – Glen Swan
    Sep 24, 2014 at 11:45
  • 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!
    – Yamada
    Sep 24, 2014 at 12:08
  • No problems! Sometimes simple is the best way to go.
    – Glen Swan
    Sep 24, 2014 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.