CODEWITHSUNDEEP
sqlms-access
Brad
Brad

Reputation: 157

SQL. This one involves multiplying two columns from different tables

First I have to find the Salesman ID, salespersons names (first and last), their SIN number, the quantity sold, the unit price and the total $ in sales (and I have to label this field as TotalSales) for each product.

Here is the data:

enter image description here

Here is what I have done so far, let me know if it is wrong:

SELECT 
    s.SalesmanID, s.FirstName, s.LastName, s.SIN, 
    ps.QuantitySold, ps.UnitSalesPrice, 
    (this is where I’m stuck, I must have to create a new column called TotalSales here)

Let me know if you can help!

Upvotes: 0

Views: 200

Answers (1)

patelb
patelb

Reputation: 2581

SELECT s.SalesmanID, max(s.FirstName) first_name, max(s.LastName) last_name, max(s.SIN) sin, sum(ps.QuantitySold) sold_qty, sum(ps.quantitysold * ps.UnitSalesPrice) total_sales_AMT from salesmen s join productsales PS on s.salesmanid = PS.sellerid group by s.salesmanid

You need to do a join with a group by in order to total the sales quantity and sales prices at the salesman level. See code above.

The reason for the join and group by is because i assume that there is a one to many relationship between the salesmen and productsales.

If you need to do it at the salesman and product level then you want to expand the grouping. See below.

SELECT s.SalesmanID, ps.productid, max(s.FirstName) first_name, max(s.LastName) last_name, max(s.SIN) sin, sum(ps.QuantitySold) sold_qty, sum(ps.quantitysold * ps.UnitSalesPrice) total_sales_AMT from salesmen s join productsales PS on s.salesmanid = PS.sellerid group by s.salesmanid, ps.productid

One more note is that replace the join with a left outer join if you want to get all salesmen and not only the ones that had a sale

Upvotes: 2

Related Questions