Joining Tables , Data Between Dates

I am practicing with northwind database: I am quite new to sql. Question I am trying to solve is : Q. Total Sales for each customer in October 1996 (based on OrderDate). Show the

Solution 1:

I suspect is it just the method of calculation inside the sum function

SELECT
    c.customerid
  , c.companyname
  , o.orderdate
  , SUM((od.unitprice * od.Quantity) * (1 - od.Discount)) AS totalsales
FROM customers AS c
INNER JOIN orders AS o ON o.customerid = c.CustomerID
INNER JOIN [Order Details] AS od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '1996-10-01'AND o.orderdate < '1996-11-01' -- move up one day, use less thanGROUPBY
    c.customerid
  , c.companyname
  , o.orderdate
ORDERBY
    totalsales DESC
;

• (od.unitprice * od.Quantity) provides total discounted price, then
• the discount rate is (1 - od.Discount)
• multiply those (od.unitprice * od.Quantity) * (1 - od.Discount) for total discounted price

Please note I have changed the syntax of the joins! PLEASE learn this more modern syntax. Don't use commas between table names in the from clause, then conditions such as AND o.customerid = c.CustomerID move to after ON instead of within the where clause..

Also, the most reliable date literals in SQL Server are yyyymmdd and the second best is yyyy-mm-dd. It's good to see you using year first, but I would suggest using dashes not slashes, or (even better) no delimiter. e.g.

WHERE o.OrderDate >='19961001'AND o.orderdate <'19961101'

Also note that I have removed the <= and replaced it with < and moved that higher date to the first of the next month. It is actually easier this way as every month has a day 1, just use less than this higher date.