How to generate simple Packing List with MySQL ?

Question

Hi,

I need help on how to create a packing list of a shipment with MySQL.

Let's say i have 32 boxes of keyboard ready to ship, the master carton can contain 12 boxes.

I only have value 32 boxes and volume of 12. The other value in result below is generated by sql command. Not coming from record.

So this easily calculate that the number of master carton would be 3 master cartons, with one as a non-standard quantity. How to perform query on this ?

As i would like to be this result:

+----------+---------------+-------------------+--------+------------+---------+
| Quantity | Standard_Qty  | Non_Standard_Qty  | Box_N  | Box_Total  | RowType |
+----------+---------------+-------------------+--------+------------+---------+
|       12 |             1 |                 0 |      1 |          3 | Detail  |
|       12 |             1 |                 0 |      2 |          3 | Detail  |
|        8 |             0 |                 1 |      3 |          3 | Detail  |
|       32 |             2 |                 1 |        |            | Summary |
+----------+---------------+-------------------+--------+------------+---------+

It looks like two query i know and probably the use of FLOOR command, in which i was teach in here. How to make this result?

Thanks in advance. Stephen

Answer 1

SQL isn't optimized for processing data as much as retrieving and storing it. For that reason, any answer you get will be relatively complicated (I'd think). Much simpler is to process this in whatever language you're executing the SQL query in. The pseudocode might look something like:

int num_keyboards = SELECT COUNT(*) FROM keyboards

int num_standard_packages = num_keyboards / 12
int keyboards_in_non_standard_package = num_keyboards % 12 (if 0 then discard)

Answer 2

Thanks Kai,

But i would like to generate it by SQL command, not by client programming. I was stack in the middle as like this. It's not a complete column anyway, my intend is in my original post.

mysql> SELECT
    -> art_name,
    -> color_code,
    -> volume,
    -> SUM(quantity) AS total,
    -> FLOOR(SUM(quantity) / volume) as boxes_sq,
    -> (SUM(quantity) % volume) as box_nsq_contain
    -> FROM order_main
    -> WHERE order_main_id = "11"
+----------+------------+--------+-------+----------+-----------------+
| art_name | color_code | volume | total | boxes_sq | box_nsq_contain |
+----------+------------+--------+-------+----------+-----------------+
| KEYBOA   | CAR        |     12 |     5 |        0 |               5 | 
| KEYBOA   | CAR        |     12 |     9 |        0 |               9 | 
| KEYBOA   | CAR        |     12 |    15 |        1 |               3 | 
| KEYBOA   | CAR        |     12 |    20 |        1 |               8 | 
| KEYBOA   | CAR        |     12 |    12 |        1 |               0 | 
| KEYBOA   | CAR        |     12 |     6 |        0 |               6 | 
| KEYBOA   | CAR        |     12 |     3 |        0 |               3 | 
| KEYBOA   | CSM        |     12 |     5 |        0 |               5 | 
| KEYBOA   | CSM        |     12 |     9 |        0 |               9 | 
| KEYBOA   | CSM        |     12 |    17 |        1 |               5 | 
| KEYBOA   | CSM        |     12 |    21 |        1 |               9 | 
| KEYBOA   | CSM        |     12 |    14 |        1 |               2 | 
| KEYBOA   | CSM        |     12 |     6 |        0 |               6 | 
| KEYBOA   | CSM        |     12 |     3 |        0 |               3 | 
+----------+------------+--------+-------+----------+-----------------+