Select count of grouped Products AND the cheapest price for each product-CodePudding

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

CodePudding user response：

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)