Total amount of sales done for each product using SQL

Question

Here is the structure of 1st Table called Product.

PRODID     PDESC      PRICE    CATEGORY      DISCOUNT
101        BALL       10       SPORTS        5
102        SHIRT      20       APPAREL       10

Here is the structure of 2nd table called SaleDetail.

SALEID     PRODID     QUANTITY
1001       101        5
1001       101        2
1002       102        10
1002       102        5

I am trying to get total sales amount for each product by joining 2 tables. Here is the SQL i tried but its not giving correct result.

select a.prodid, 
       (sum((price - discount))), 
       sum(quantity), 
       (sum((price - discount))) * sum(quantity) 
  from product a 
  join saledetail b on a.prodid = b.prodid 
group by a.prodid

2nd column of the query is giving incorrect final price. Please help me correct this SQL.

Edit your question and show the results you want. What do you mean by "final price"? — Gordon Linoff, Oct 29 '20 at 12:36
Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Oct 29 '20 at 12:37

score 1 · Answer 1 · answered Oct 29 '20 at 12:58

Please find an indicative answer to your question in the fiddle.

A problem stems from the aggregation of the difference of price. In case that the same product has two different prices, then these prices would be aggregated to one.

Moreover, you multiple the sums of the prices and quantities, while you need to perform the calculation on every sample. Look at the answer by @DanteTheSmith.

You might consider to use the SaleDetail table on the left side of your query.

SELECT SD.PRODID, 
  P.Price-P.Discount AS Final_Price, 
  SUM(SD.QUANTITY) AS Amount_Sold, 
  SUM((P.Price-P.Discount)*SD.QUANTITY) AS Sales_Amount
FROM SaleDetail AS SD
JOIN Product AS P
ON SD.PRODID = P.PRODID
GROUP BY SD.PRODID, P.Price-P.Discount

DanteTheSmith · Answer 2 · 2020-10-29T13:05:27.717

It would help if you built the example in SQL fiddle or gave the creates for the tables, but if I have to guess your problem is:

(sum((price - discount))) * sum(quantity)

needs to be:

sum((price - discount) * quantity)

(price - discount) * quantity is the function you wanna apply PER ROW of the joined table then you wanna add all those up with SUM() when grouping by prodid.

Furthermore, you can notice that (price - discount) needs to be done ONLY ONCE PER ROW so a quicker version would be to do:

(price-discount) * sum(quantity)

That would give you the total money earned for that product across all the sales you made, and I am guessing this is what you want?

I just notice you have a problem with 2nd column, dunno if that has been in question all along:

(sum((price - discount)))

Why are you summing? Do you want the money earned per product per unit of the product? Well guess what, your price is the same all the time, same as your discount so you can simply go with:

(price-discount) as PPP

NOTE: This assumes the discount is numerical (not percentage) and is applicable to all your sales, also the price is forever the same all which is not real life like.

Total amount of sales done for each product using SQL

2 Answers2