CODEWITHSUNDEEP
mysql
abiku
abiku

Reputation: 2246

Get products summary from mysql table

I have to write a query which seem to be bit too complicated to me. I have order_products table with these sample records:

| ORDER_PRODUCTS_ID(auto)  | PRODUCT_ID    | PRICE | QUANTITY
| 1                        | 2             | 100   | 2
| 2                        | 2             | 100   | 3
| 3                        | 2             | 50    | 1
| 4                        | 3             | 200   | 4
| 5                        | 3             | 200   | 2

I need a query for rapport where I can have summary of all most often purchased products group by products ids and prices (because one product can have different prices in each order as you see on the example) I need results to be like this:

PRODUCT_ID | QUANTITY_SUM | PRICE      | TOTAL SUM PRICE
3          | 6            | 200        | 400
2          | 5            | 100        | 200
2          | 1            | 50         | 50

Im trying with a query like:

select price, product_id, SUM(quantity) as quantity_sum ,
  price*quantity_sum as total_sum_price from dr_orders_products
   group by product_id , price order by quantity_sum desc

but i gives me error: 'quantity_sum is unknown column'. was trying also something like this:

select price, product_id, SUM(quantity) as quantity_sum ,
      price*(select SUM(quantity) from dr_orders_products where product_id =product_id ans price = price  ) as total_sum_price from dr_orders_products
       group by product_id , price order by quantity_sum desc

but the results are some worng higher values. I will really appreciate any help on this

Upvotes: 0

Views: 100

Answers (3)

Paul
Paul

Reputation: 9012

For this kind of reports, you should probably use MySQL Views. A view is like query that is updating its result frequently.

CREATE VIEW product_summary AS
    SELECT 
        price, 
        product_id, 
        SUM(quantity) AS quantity_sum,
        price*SUM(quantity) AS total_sum_price
    FROM dr_orders_products
    GROUP BY product_id, price;

Now, if you need to get the top product, you can either

SELECT product_id FROM product_summary ORDER BY quantity_sum DESC LIMIT 1;

to get the product (at a fixed price) which has been sold most often. To get the product (at a fixed price) which generated the most income, you can use

SELECT product_id FROM product_summary ORDER BY total_sum_price DESC LIMIT 1;

Of course, you can take it even further by creating a second view out from the first on which contains only one row per product ID:

CREATE VIEW product_distinct_summary AS 
    SELECT 
        product_id,
        SUM(quantity_sum) AS quantity_sum,
        SUM(total_sum_price) AS total_sum_price
    FROM product_summary;

By using the two former SELECT queries for the top product at a fixed price on this second view, you will get the products which have been sold most often or generated the most income regardless of their individual price.

Upvotes: 1

Juan de Parras
Juan de Parras

Reputation: 778

You cant call an alias field in the select statement, so to use quality_sum you need to call them as original name. But you are doing a SUM in quantity field, then you only need use the same quantity in order to calc the total.

something like this:

select price, product_id, SUM(quantity) as quantity_sum ,
       price*SUM(quantity) as total_sum_price from dr_orders_products
group by product_id , price 
order by quantity_sum desc

Upvotes: 1

Juan
Juan

Reputation: 3705

Try this

select price, product_id, SUM(quantity) as quantity_sum ,
       price*SUM(quantity) as total_sum_price 
from dr_orders_products
group by product_id , price 
order by SUM(quantity) desc

You cannot reference a column you are creating in the SELECT in that same select.

Upvotes: 2

Related Questions