CODEWITHSUNDEEP
mysqlsqlselectgroup-bymin
Quiche
Quiche

Reputation: 301

SQL - multi SELECT and GROUP BY to get MIN(value) of table

I am working on a price comparator website.

I have the following tables: table item_price:

|| Set_id ||store_id ||currency_id ||set_price ||
||  5252  ||    1    ||     1      ||   499.99 ||
||  5252  ||    2    ||     1      ||   480    ||
||  5252  ||    3    ||     1      ||   489    ||
||  5252  ||    4    ||     2      ||   450    ||
||  5252  ||    5    ||     2      ||   445    ||
||  5252  ||    6    ||     2      ||   470    ||
||  5253  ||    1    ||     1      ||   389.99 ||
||  5253  ||    2    ||     1      ||   392    ||
||  5253  ||    3    ||     1      ||   400    ||
||  5253  ||    4    ||     2      ||   360    ||
||  5253  ||    5    ||     2      ||   370    ||
||  5253  ||    6    ||     2      ||   380    ||

and set_currency

||currency_id||currency_name||conversion_rate||
||    1      ||    EUR      ||      1        ||
||    1      ||    GBP      ||      0.9      ||

My goal is to get a query that brings me back the cheapest price in each currency in the following format

|| Set_id || store_id || Min_price_eur || Min_price_gbp_in_eur ||
||  5252  ||    5     ||      480      ||        400.5         ||
||  5253  ||    4     ||    389.99     ||        324           ||

so far, I only manage to do achieve half of the goal:

|| Set_id || store_id || Min_price_eur ||
||  5252  ||    1     ||      480      ||
||  5253  ||    1     ||      392      ||

using the following request:

SELECT 
    set_id,
    min(set_price) AS Min_price_eur,

FROM `item_price`
WHERE currency_id=1
GROUP BY set_id

to create the last column, i tired many things such as :

SELECT 
    set_id,
    min(set_price) AS Min_price_eur,
    (SELECT MIN(set_price) FROM 'item_price' WHERE currency_id=2) * (SELECT conversion_rate FROM set_currency WHERE currency_id = 2) AS  Min_price_gbp_in_eur

FROM `item_price`
WHERE currency_id=1
GROUP BY set_id

but i don't manage. if anyone could offer me some help, i would really appreciate.

Thank you

ps: here are the code to create the tables:

item price:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


CREATE TABLE `item_price` (
  `set_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `currency_id` int(10) UNSIGNED NOT NULL,
  `set_price` float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `item_price` (`set_id`, `store_id`, `currency_id`, `set_price`) VALUES
(5252, 1, 1, 499.99),
(5252, 2, 1, 480),
(5252, 3, 1, 489),
(5252, 4, 2, 450),
(5252, 5, 2, 445),
(5252, 6, 2, 470),
(5253, 1, 1, 389.55),
(5253, 2, 1, 392),
(5253, 3, 1, 400),
(5253, 4, 2, 360),
(5253, 5, 2, 370),
(5253, 6, 2, 380);


ALTER TABLE `item_price`
  ADD PRIMARY KEY (`set_id`,`store_id`) USING BTREE,
  ADD KEY `store_id` (`store_id`),
  ADD KEY `currency_id` (`currency_id`);
COMMIT;

and currency:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";



CREATE TABLE `set_currency` (
  `currency_id` int(10) UNSIGNED NOT NULL,
  `currency_name` char(3) NOT NULL,
  `conversion_rate` float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `set_currency` (`currency_id`, `currency_name`, `conversion_rate`) VALUES
(1, 'EUR', 1),
(2, 'GBP', 0.9);

ALTER TABLE `set_currency`
  ADD PRIMARY KEY (`currency_id`);
COMMIT;

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This problem to me looks like aggregation with some arithmetic:

select ip.set_id, ip.store_id,
       min(case when currency_name = 'EUR' then ip.set_price end) as min_price_eur,
       min(case when currency_name = 'GBP' then ip.set_price / c.conversion_rate end) as min_gpbprice_eur
from item_price ip left join
     set_currency c
     on ip.currency_id = c.currency_id
group by ip.set_id, ip.store_id

Upvotes: 1

Related Questions