SQL Server : search among groups based on multiple conditions

I have the following Product table. I’m trying to find the products based on multiple criteria.

Here’s my sample data:

+-----------+------------+------------------+----------------+-----------------+-------+
| productid |  barcode   |   product_name   | is_product_new | premium_service | price |
+-----------+------------+------------------+----------------+-----------------+-------+
|         1 | 1122334455 | rubber duck      |              0 |               0 |  3,00 |
|         2 | 1122334455 | rubber duck      |              1 |               0 |  4,00 |
|         3 | 1122334455 | rubber duck      |              1 |               0 |  5,00 |
|         4 | 1122334455 | rubber duck      |              1 |               1 |  6,00 |
|         5 | 2233445566 | barbie doll      |              1 |               0 | 10,00 |
|         6 | 2233445566 | barbie doll      |              0 |               0 |  8,00 |
|         7 | 3344556677 | actionman figure |              1 |               1 | 22,00 |
|         8 | 3344556677 | actionman figure |              1 |               0 | 18,00 |  
|         9 | 3344556677 | actionman figure |              0 |               0 |  6,00 |
|        10 | 3344556677 | actionman figure |              0 |               0 |  5,00 |
+-----------+------------+------------------+----------------+-----------------+-------+

There are a total of three products.

I want to search the products with lowest new price, lowest used price, and premium price among premium service offered products.

My expected result is:

+-----------+------------+------------------+------------------+------------------+-----------------+
| productid |  barcode   |   product_name   | lowest_old_price | lowest_new price |   premium_price |
+-----------+------------+------------------+------------------+------------------+-----------------+
|         1 | 1122334455 | rubber duck      |             3.00 |             4.00 |            6.00 |
|         7 | 3344556677 | actionman figure |             5.00 |            18.00 |           22.00 |
+-----------+------------+------------------+------------------+------------------+-----------------+

I’ve tried to write a query with group by and having clause but my results didn’t make any sense! Even I’m not sure which functions/clause I need to use!

Need your help…

Answer

Hmmm . . . if I understand correctly, this is conditional aggregation with a having clause:

select min(productid) as productid, barcode, product_name
       min(case when is_product_new = 0 then price end) as old_price,
       min(case when is_product_new = 1 then price end) as new_price,
       min(case when premium_service = 1 then price end) as premium_price
from products p
group by productid, barcode, product_name
having max(premium_service) = 1;

If premium_service is a bit, you can convert to an integer:

having max(convert(int, premium_service)) = 1