0

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 result in CustomerID, CompanyName, and [total sales], sorted in [total sales] in Decending order.

I have used this code but doesn't seems to be correct , please advise.

select c.customerid , c.companyname , o.orderdate , sum(od.unitprice *od.Quantity*1-od.Discount) as totalsales
 from customers as c , orders as o , [Order Details] as od
 where  o.customerid = c.CustomerID
    and o.OrderID = od.OrderID
    and  o.OrderDate >=  '1996/10/01' and  o.orderdate <= '1996/10/31'

 group by c.customerid , c.companyname, o.orderdate
 order by totalsales desc
;


*******************************
tauqeer
  • 7
  • 2
  • Please show an example and what it produced. – Smartcat Oct 30 '18 at 05:31
  • Tip of today: Use the modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Oct 30 '18 at 06:48

1 Answers1

2

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 than
GROUP BY
    c.customerid
  , c.companyname
  , o.orderdate
ORDER BY
    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.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • why not use between to check dates – Ravi Oct 30 '18 at 07:11
  • there's many referenceson this subject, and one comment here won't be enough to explain. Perhaps best known at SO is Aron Bertrand's reference on this topic but I will quote [Itzik Ben-Gan](http://sqlmag.com/t-sql/t-sql-best-practices-part-2) "`the best practice with date and time ranges is to avoid BETWEEN and to always use the form: WHERE col >= '20120101' AND col < '20120201' This form works with all types and all precisions, regardless of whether the time part is applicable.`" and this also: https://stackoverflow.com/questions/16347649/sql-between-not-inclusive/26190806#26190806 – Paul Maxwell Oct 30 '18 at 07:20