How to use SUM and MAX in SQL-CodePudding

I want to give the name of the product that has been sold the most overall with its sales quantity. Total Quantity is the sum of products sold - that are the products whose order status (order_status) is Paid or Shipped. I have the following tables:

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR NOT NULL,
product_price numeric NOT NULL,
product_type product_types NOT NULL,
product_created_at TIMESTAMP NOT NULL DEFAULT NOW()


CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_store_id INT NOT NULL REFERENCES stores,
order_user_id INT NOT NULL REFERENCES users,
order_status order_states NOT NULL,
order_created_at TIMESTAMP NOT NULL DEFAULT NOW()

CREATE TABLE order_items (
order_id INT references orders,
product_id INT references products,
quantity INT DEFAULT 1 CHECK ( quantity > 0 ),
PRIMARY KEY (order_id, product_id)

I have written the query:

SELECT products.product_name, maxam
  FROM  products, (SELECT SUM(quantity) AS total FROM order_items) AS foo
  WHERE orders.order_id = order_items.order_id AND orders.order_status IN ('Paid', 'Shipped')
  GROUP BY orders.order_status
  HAVING maxam = MAX(total)

, but I think there are a lot of mistakes (working with sql for the first time)

CodePudding user response：

try this

SELECT top 1 products.product_name,  SUM(quantity) as QTY
  FROM  
orders inner join
order_items
on orders.order_id = order_items.order_id 

order_items inner join
products
on order_items.product_id  = products.product_id 

WHERE orders.order_status IN ('Paid', 'Shipped')
  GROUP BY products.product_name
 order by qty desc

CodePudding user response：

Working query:

SELECT products.product_name , SUM(quantity) AS QTY 
  FROM orders JOIN  order_items ON orders.order_id = order_items.order_id JOIN products ON order_items.product_id  = products.product_id  
  WHERE order_status IN ('Paid', 'Shipped')
  GROUP BY products.product_name
  ORDER BY QTY desc LIMIT 1;