1

Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff.

There are 3 categories: setup, build, and finishing. I'd like to get a total for each category per order. Testing shows my sql foo is rusty and looking for guidance on how to roll this stuff up. I'm already querying a view to make this simpler.

http://sqlfiddle.com/#!9/acb02/3

CREATE TABLE `OrderLineItems` (
  `orderId` INT(11) NOT NULL,
  `dt` DATETIME,
  `buyerEmail` VARCHAR(512),
  `orgId` INT(11) NOT NULL,
  `description` VARCHAR(512),
  `application` VARCHAR(512),
  `category` VARCHAR(30),
  `quantity` INT(5),
  `price` DECIMAL(9,2),
  `subtotal` DECIMAL(19,2)
)

Looking for one row per order in the result, with the total for each price category. A total cost for the order would be bonus.

 Order Id | Setup Total | Build Total | Finishing Total | All Total
--------------------------------------------------------------------

Also, adding in the date, is it possible to get a grouping by month for the 3 categories?

 Month | Setup Tot | Build Tot | Finishing Tot | All Tot
---------------------------------------------------------
 1/16 | 
 2/16 |
1

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*), 
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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