Write a query to determine how many product have been sold with profit-CodePudding

I am very new to SQL. I have three tables, transactions, products, and customers. I want to know how many products have been sold with profit.

SELECT t.product_id, p.id,  sum(t.total_price / t.quantity) - p.price As profit 
From transactions as t , products As p
INNER JOIN transactions
on t.product_id = p.id
GROUP by t.product_id

I have only a total price column in my transactions table. Should I divide total_price to quantity or * ? How about my all query?

CodePudding user response：

SELECT t.product_id,sum(t.total_price / t.quantity - p.price) as profit
  FROM transactions as t
     INNER JOIN product as p on t.id = p.id
       GROUP by t.product_id
         order by t.product_id

Can you try this way

CodePudding user response：

SELECT t.product_id,sum(t.total_price / t.quantity - p.price) as profit, 
  count(t.quantity) as sold_count
    FROM transactions as t
      INNER JOIN product as p on t.id = p.id
        GROUP by t.product_id
          order by t.product_id