Getting highest calculated score of GROUP BY in mysql

Question

I'm trying to retrieve the best suited price for a product in each quantity depending on the customer and/or his customer group. To do so, I use a weight based system: the matching customer group is more important than the matching customer, so if two rows collide, we should get the row corresponding to the customer group id.

Here's an example:

Customer n°1 is part of Customer group n°2
Product prices:
 A - 90€ for customer n°1 (when buying at least 2 of the same product)
 B - 80€ for customer group n°2 (when buying at least 2 of the same product)
So the price shown to the customer n°1 should be 80€

He's my query:

SELECT 
    MAX(IF(t.customer_id = 1, 10, 0) + IF(t.customer_group_id = 1, 100, 0)) as score, 
    t.*
FROM tierprice t
WHERE t.product_variant_id = 110 
    AND (t.customer_id = 1 OR t.customer_id IS NULL) 
    AND (t.customer_group_id = 1 OR t.customer_group_id IS NULL)
GROUP BY t.product_variant_id, t.qty

The problem I'm having is that the correct score is shown in the result row (here: 100), but the row for the given score is not correct. I'm guessing it has something to do with the MAX in the SELECT and the GROUP BY, but I don't know how to assign the score to the row, and then take the highest.

Here's a fiddle :

CREATE TABLE `tierprice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_variant_id` int(11) DEFAULT NULL,
  `customer_group_id` int(11) DEFAULT NULL,
  `price` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `no_duplicate_prices` (`qty`,`product_variant_id`,`customer_group_id`),
  KEY `IDX_BA5254F8A80EF684` (`product_variant_id`),
  KEY `IDX_BA5254F8D2919A68` (`customer_group_id`),
  KEY `IDX_BA5254F89395C3F3` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tierprice` (`id`, `product_variant_id`, `customer_group_id`, `price`, `qty`, `customer_id`)
VALUES
    (1, 110, NULL, 8000, 2, 1),
    (2, 110, 1, 7000, 2, NULL),
    (3, 110, 1, 6000, 5, NULL),
    (4, 110, NULL, 5000, 5, 1),
    (5, 111, 1, 8000, 2, NULL),
    (6, 111, NULL, 6000, 2, 1),
    (7, 111, 1, 7000, 6, NULL),
    (8, 111, NULL, 5000, 6, 1);

http://sqlfiddle.com/#!9/7bc0d9/2

The price ids that should come out in the result should be ID 2 & ID 3.

Thank you for your help.

You mention a group a customer is associated with. Can you please provide the sample customer table showing their ID and GroupID they are affiliated with? In this sample, all your data is referring to just customer 1 and Group 1 which is far from real scenario of all data. You dont want to have a "hard-coded" solution with specific IDs embedded throughout. Your group pricing also has Qty price-break points of 2 and 5, and don't want fixed there either. — DRapp, Nov 12 '20 at 16:48
Also, where WOULD the Qty actually ordered be coming from as to not be fixed... Order detail? Show that source too (orderDetail table with customer, product, qty ordered). — DRapp, Nov 12 '20 at 16:54

forpas · Answer 1 · 2020-11-12T17:29:55.330

The only valid columns that your query can return are product_variant_id, qty, which you use in GROUP BY clause, and the aggregated column score.
Because of t.* you get all the columns of the table but the values chosen are nondeterministic, for the other columns, as it is explained in MySQL Handling of GROUP BY.
What you can do is join your query to the table like this:

SELECT t.*
FROM tierprice t
INNER JOIN (
  SELECT product_variant_id, qty, 
         MAX(IF(customer_id = 1, 10, 0) + IF(customer_group_id = 1, 100, 0)) as score
  FROM tierprice 
  WHERE product_variant_id = 110 
    AND (customer_id = 1 OR customer_id IS NULL) 
    AND (customer_group_id = 1 OR customer_group_id IS NULL)
  GROUP BY product_variant_id, qty
) g ON g.product_variant_id = t.product_variant_id 
   AND g.qty = t.qty
   AND g.score = IF(t.customer_id = 1, 10, 0) + IF(t.customer_group_id = 1, 100, 0)
WHERE (t.customer_id = 1 OR t.customer_id IS NULL) 
  AND (t.customer_group_id = 1 OR t.customer_group_id IS NULL)

See the demo.
Results:

> id | product_variant_id | customer_group_id | price | qty | customer_id
> -: | -----------------: | ----------------: | ----: | --: | ----------:
>  2 |                110 |                 1 |  7000 |   2 |        null
>  3 |                110 |                 1 |  6000 |   5 |        null

Lukasz Szozda · Accepted Answer · 2020-11-12T17:59:29.070

Provided query is not a valid query from SQL standard's perspective:

SELECT 
    MAX(IF(t.customer_id = 1, 10, 0) + IF(t.customer_group_id = 1, 100, 0)) as score, 
    t.*
FROM tierprice t
WHERE t.product_variant_id = 110 
    AND (t.customer_id = 1 OR t.customer_id IS NULL) 
    AND (t.customer_group_id = 1 OR t.customer_group_id IS NULL)
GROUP BY t.product_variant_id, t.qty;

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Related: Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

It could be rewritten using windowed functions(MySQL 8.0 and above):

WITH cte AS (
 SELECT t.*, ROW_NUMBER() OVER(PARTITION BY product_variant_id, qty 
             ORDER BY IF(t.customer_id=1,10,0)+IF(t.customer_group_id=1,100,0) DESC) AS rn
 FROM tierprice t
 WHERE t.product_variant_id = 110 
     AND (t.customer_id = 1 OR t.customer_id IS NULL) 
     AND (t.customer_group_id = 1 OR t.customer_group_id IS NULL)
)
SELECT *
FROM cte
WHERE rn = 1;

db<>fiddle demo

Getting highest calculated score of GROUP BY in mysql

2 Answers2