Question 113620 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select count of grouped Products AND the cheapest price for each product

I’m trying to select the distinct products, grouped by all columns except the ID, and the cheapest bid for that type of product. Sometimes, there may be no bids for that product, so I’ll need to left join to account for those.

I’ll add the desired output below and also a sort-of pseudo query to help explain.

If it helps, you can imagine a product search page. When the user searches, it groups all the same products together. These products can be sold by many different sellers but we want to only show it once within the results, along with the cheapest price for that product at the time of the search.

Data I want returned

SELECT count(Name), Size, Weight, Color, BidID, Status, min(Price)
-- joins go here --
-- group by Size, Weight, Color, BidID, Status

Will also require WHERE clauses, which are built based on filters used by end user. Examples:

WHERE Status = 'Active' and Weight = '1kg'
WHERE Status = 'Active'
WHERE Size = '1m' and Weight in ('1kg', '2kg')

Products Table

| ProductID (PK) | Name        | Size | Weight | Color  |
| --------       | --------    | -----| -----  | -----  | 
| 1              | Black Table | 1m   | 2kg    | Black  | 
| 2              | Purple Table| 1m   | 3kg    | Purple | 
| 3              | Small Table | 1m   | 3kg    | Black  | 
| 4              | Small Table | 1m   | 3kg    | Black  | 
| 5              | Black Table | 1m   | 2kg    | Black  | 
| 6              | Purple Table| 1m   | 3kg    | Purple |
| 7              | Small Table | 1m   | 3kg    | Black  | 
| 8              | Small Table | 1m   | 3kg    | Black  |

Bids Table

| BidID (PK)     | ProductID   | Status      | Price   |
| --------       | -------     | --------    | -----   |
| 1              |  1          | Active      | 123.5   |
| 2              |  1          | Active      | 325.99  |
| 3              |  1          | Active      | 85.99   |
| 4              |  3          | Cancelled   | 315.99  |
| 5              |  4          | Active      | 113.5   |
| 6              |  3          | Cancelled   | 305.99  |
| 7              |  1          | Active      | 82.99   |
| 8              |  2          | Active      | 345.99  |

DESIRED OUTPUT EXAMPLE

| Count          | Name        | Size | Weight | Color  | BidID |CheapestPrice |
| --------       | --------    | -----| -----  | -----  | ----- | -----     |
| 38             | Black Table | 1m   | 2kg    | Black  |  1    | 123.5
| 21             | Purple Table| 1m   | 3kg    | Purple |  2    | 89.95
| 13             | Small Table | 1m   | 3kg    | Black  |  3    | 65.94
Tags:

2

Answers


  1. 0

    Assuming you have version 8 or above (and you should by now) allocate a row number to the lowest bid an union products with no bids

    DROP TABLE IF EXISTS PRDUCTS,BIDS;

CREATE TABLE Prducts
( ProductID INT, Name  VARCHAR(20), Size VARCHAR(20), Weight VARCHAR(20), Color  VARCHAR(20));
INSERT INTO PRDUCTS VALUE
( 1              , 'Black Table' , '1m'   , '2kg'    , 'Black'  ), 
( 2              , 'Purple Table', '1m'   , '3kg'    , 'Purple' ), 
( 3              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ), 
( 4              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ), 
( 5              , 'Black Table' , '1m'   , '2kg'    , 'Black'  ), 
( 6              , 'Purple Table', '1m'   , '3kg'    , 'Purple' ),
( 7              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ), 
( 8              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ),
( 9              , 'BOMBASTO'    , '1m'   , '3kg'    , 'Black'  ); 

CREATE TABLE Bids
( BidID    INT     , ProductID  INT  , Status VARCHAR(20) ,     Price DECIMAL(10,2));
INSERT INTO BIDS VALUES
( 1              ,  1          , 'Active'      , 123.5   ),
( 2              ,  1          , 'Active'      , 325.99  ),
( 3              ,  1          , 'Active'      , 85.99   ),
( 4              ,  3          , 'Cancelled'   , 315.99  ),
( 5              ,  4          , 'Active'      , 113.5   ),
( 6              ,  3          , 'Cancelled'   , 305.99  ),
( 7              ,  1          , 'Active'      , 82.99   ),
( 8              ,  2          , 'Active'      , 345.99  );

WITH CTE AS
(SELECT P.PRODUCTID PPID,NAME,SIZE,WEIGHT,COLOR
       ,B.BIDID,B.PRODUCTID AS BPID,STATUS,PRICE
         ,ROW_NUMBER() OVER (PARTITION BY NAME,SIZE,WEIGHT,COLOR ORDER BY PRICE) RN
FROM PRDUCTS P
JOIN BIDS B ON B.PRODUCTID = P.PRODUCTID
) 
SELECT PPID,NAME,SIZE,WEIGHT,COLOR,PRICE FROM CTE 
WHERE  RN = 1
UNION ALL
SELECT DISTINCT PRODUCTID,NAME,SIZE,WEIGHT,COLOR,NULL
FROM   PRDUCTS P
WHERE  NOT EXISTS
        (SELECT 1 FROM CTE WHERE CTE.NAME = P.NAME      AND
                                         CTE.WEIGHT = P.WEIGHT  AND
                                         CTE.COLOR = P.COLOR);

+------+--------------+------+--------+--------+--------+
| PPID | NAME         | SIZE | WEIGHT | COLOR  | PRICE  |
+------+--------------+------+--------+--------+--------+
|    1 | Black Table  | 1m   | 2kg    | Black  |  82.99 |
|    2 | Purple Table | 1m   | 3kg    | Purple | 345.99 |
|    4 | Small Table  | 1m   | 3kg    | Black  | 113.50 |
|    9 | BOMBASTO     | 1m   | 3kg    | Black  |   NULL |
+------+--------------+------+--------+--------+--------+
4 rows in set (0.003 sec)
    Login or Signup to reply.
  2. 0

    Whole Query :

    SELECT
    Count( p.ProductID ) AS Count,
    p.`Name`,
    p.Size,
    p.Weight,
    p.Color,
    b.BidID,
    b.Price 
FROM
    products AS p
    LEFT JOIN (
SELECT
    MIN( bi.Price ) AS Price,
    bi.BidID AS BidID,
    bi.ProductID AS ProductID 
FROM
    bids AS bi
    JOIN (
SELECT
    bc.TimeOfBid AS bids_last_filled 
FROM
    bids AS bc 
WHERE
    bc.ProductID = ProductID 
    AND bc.`Status` = 'Filled' 
ORDER BY
    bc.TimeOfBid DESC 
    LIMIT 1 
    ) BLF 
WHERE
    bi.`Status` = 'Active' 
    AND bi.TimeOfBid > BLF.bids_last_filled 
GROUP BY
    bi.ProductID 
    ) b ON p.ProductID = b.ProductID 
GROUP BY
    p.`Name`

    The above code is for :
    Select count of grouped Products AND the cheapest price for each product

    Check it out.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search