CODEWITHSUNDEEP
sql-server
SwedishPotato
SwedishPotato

Reputation: 43

Total amount of sales in a month SQL Server Management Studio

Don't know if I overdid it with the normalization but there are 4 tables:

I want something like this:

( Employee_Name, Month, Total_Sales_Made_In_That_Month_In_Dollar )

I think you can get the total of each order from 2 tables with something like:

SELECT SUM((Order_Each_Product.Quantity) * (Product.Price)) 
FROM Order_Each_Product 
INNER JOIN Product ON Order_Each_Product.ProductID = Product.ProductID
GROUP BY OrderID

But how do you group that again by Employee and get the SUM of the SUM?

Upvotes: 4

Views: 128

Answers (2)

Steve
Steve

Reputation: 960

Forpas beat me to it while I was writing the code!

SELECT
    Employee.Name, Year, Month, SUM(Quantity * Price) AS Total_Sales_In_Month
FROM 
    Order_Details

LEFT JOIN Employee
    ON (Order_Details.Employee_ID = Employee.ID)

LEFT JOIN Order_Each_Product
    ON (Order_Details.ID = Order_Each_Product.OrderID)

LEFT JOIN Product
    ON (Order_Each_Product.ProductID = Product.ID)

GROUP BY
    Employee.ID, Employee.Name, Year, Month

Just a tip also, what you are currently calling Order_Details and Order_Each_Product would normally be called order_headers and order_lines respectively.

Upvotes: 2

forpas
forpas

Reputation: 164089

Join all the tables and group by the employee's id, name, Year and Month:

select
  e.name,
  od.Year,
  od.Month,
  sum(oep.quantity * p.price) Total_Sales_Made_In_That_Month_In_Dollar
from employee e 
inner join order_details od on od.employee_id = e.id
inner join order_each_product oep on oep.orderid = od.id
inner join product p on p.id = oep.productid
group by e.id, e.name, od.Year, od.Month

Upvotes: 1

Related Questions