Query that counts different combinations

Question

First of all, my apologies for the title of this question, I don't have a better idea for the moment. Make a good suggestion and I will fix the title. (If I have permission to do that, I actually don't know.)

The situation:

I am having a tough time getting the correct SQL query done. I have a setup where people can place orders, with products etc, and they get discounts in certain circumstances.

Consider the following schema:

Product:
  [...]

Price:
  product_id: integer
  description: string
  [...]

Order:
  [...]

OrderItem:
  order_id: integer
  price_id: integer
  amount: integer

And consider the following rules:

• There are 9 different products.
• All Products have 2 Prices, one with description PriceA and one described PriceB.
• All these prices are the same per type for every product. (That is, all PriceA prices are the same, and all PriceB prices are the same.)

The problem:

For every set of 5 different products with the same price level (i.e. PriceA vs. PriceB), the total price of the order is lowered with a certain amount. I am trying to write a query that tells me how many times that happens.

Examples:

Example 1:
A user places an order:

• 5 times product1,
• 5 times product2,
• 5 times product3,
• 3 times product4,
• 3 times product5.

All at PriceA, the customer receives 3 times the discount, since there are 3 complete sets of 5

Example 2: A user places an order:

• 5 times product1,
• 5 times product2,
• 5 times product3,
• 5 times product4,
• 2 times product5,
• 2 times product6,
• 2 times product7

All the PriceA price. Now, the customer receives 5 times the discount, since there are 4 sets of 5, two involving product5, two involving product6 and one involving product7.

The struggle:

I tried this SQL:

SELECT min(amount) as amount from
    (SELECT oi.amount from `order` o
        inner join orderitem oi on oi.order_id = o.id
        inner join price p on oi.price_id = p.id AND p.description = "PriceA"
        inner join product pr on p.product_id = pr.id
        order by oi.amount desc
        limit 5) as z
    having count(amount) = 5;

This beautifully works for Example 1, but in example 2, it will give the wrong result, as it will select the first set of 5 items, and then disregard the

The question is: Is this solvable in SQL? Or would I be better of broadening my selection and doing the math by scripting? (My web application is written in PHP, so I do have room for some server-side mathematics.)

The solution:

I implemented Neil's solution; it now looks like this:

/** @var $oid integer The order ID. */

/* Select all amounts ordered per item, only for a given price title. */
$sql = <<<SQL
SELECT oi.amount as amount FROM orderitems oi
    INNER JOIN orders   o  ON oi.order_id  = o.id AND o.id = $oid
    INNER JOIN prices   p  ON oi.price_id  = p.id AND p.title = '%s'
    INNER JOIN products pr ON p.product_id = pr.id
    ORDER BY oi.amount DESC
SQL;
$discountInfo = array(
    array(
        'price'     => 'PriceA',
        'discounts' => array(
            9 => 49.50, /* Key is 'size of set', value is 'discount'. */
            5 => 23.50
        ),
    ),
    array(
        'price' => 'PriceB',
        'discounts'  => array(
            9 => 22,
            5 => 10,
        ),
    ),
);

foreach($discountInfo as $info)
{
    /* Store all ordered amounts per item in Array. */
    $arr = array();
    $result = $this->dbQuery(sprintf($sql,$info['price']));
    while ($row = mysql_fetch_assoc($result)) $arr[] = $row['amount'];

    foreach ($info['discounts'] as $am => $di)
    {
        /* For each highest set of $am items, fetch the smallest member. */
        while (count($arr) >= $am)
        {
            /* Add discount for each complete set */
            $discount += $di * $arr[$am - 1];

            /* Substract the amounts from all members of the set */
            for ($i=0; $i<=$am - 1; $i++) $arr[$i] -= $arr[$am - 1];

            /* Remove all elements that are 0 */
            foreach ($arr as $k=>$v) if ($v == 0) unset ($arr[$k]);

            /* Array is messed up now, re-sort and re-index it. */
            rsort($arr);
        } 
    } 
}

Answer 1

This is how I would do it in code: Split the items into two arrays, one for each price level. For each array of products, while there are at least five products in the array:

1. Sort the array in descending order by the number of items of product
2. Add the number of items of the fifth product in the array to the total number of discounts
3. Subtract the number of items of the fifth product in the array from the first five products in the array
4. Delete any zero elements from the array