0

How can I groupBy product_id a query on transactions but I need the products with options to be shown separately.

For example in the table transactions if I have:

ProductID Quantity Price

    1       1       10
    1       1       10
    1       1       15 (in transaction_options this has option green(price 2,5),yellow(price 2,5))
    1       1       15 (in transaction_options this has option red(price 2,5),blue(price 2,5))

I want the result to be:

 ProductID Quantity Price
    1       2       10
    1       1       15 (this has option red(price 2,5),blue(price 2,5))
    1       1       15 (this has option green(price 2,5),yellow(price 2,5))

The problem is that they all have the same product_id, and the price can be the same but for different options.

This are the tables structure:

products with id,name,price

options with id,name,price

transactions with id,product_id,quantity,price

transaction_options with id,transaction_id,option_id (association table between transactions and options

In transactions I insert the price calculated based on option (if there are any) :

product.price+[option.price] = transaction.price

Victordb
  • 519
  • 1
  • 11
  • 25
  • So a product can have a base price and 4 option prices? and the option prices are always the same? and you will only have a quantity of 1 in transactions? I think you need to add a bit more detail including sample data from product and options as a starter. – P.Salmon Aug 23 '18 at 15:27
  • The options can be unlimited and the option prices are not the same. The options prices in my example are the same to show that I cannot `groupBy` price because there could be different options. – Victordb Aug 23 '18 at 15:30
  • I don't believe this is possible since a combination of base price * quantity could result in the same value as base price + option price * quantity. – P.Salmon Aug 23 '18 at 15:39

1 Answers1

0
Select * from (select col1, col2 from myTable where myOptions is not null) union all (select col1, count(col2) as col2 from myTable where myOptions is null group by col1);
DataVader
  • 740
  • 1
  • 5
  • 19