CODEWITHSUNDEEP
sql
eri
eri

Reputation: 19

SQL Grouping by Year

Using the w3schools db, I have to create these queries. I am stuck on a couple questions and thought I'd ask..

Q2: Show top 5 employees, excluding the top employee (show employees ranked 2-5) in terms of total sales done by those employees. In the query display employee's first name, last name and TotalSales sorted in descending order. Filter the Data for only orders done in year 1996.

My code without the year filtering is:

SELECT e.LastName, e.FirstName, SUM(od.Quantity*p.Price) AS OrderTotal
FROM [Employees] AS e 
JOIN [Orders] AS o ON o.EmployeeID=e.EmployeeID
JOIN [OrderDetails] AS od ON od.OrderID=o.OrderID
JOIN [Products] AS p ON od.ProductID=p.ProductID
GROUP BY e.EmployeeID
ORDER BY OrderTotal DESC
LIMIT 4
OFFSET 1

I am not sure how to only return it with orders in 1996, when I try to Group By year 1996 doesn't seem to help..

Thank you!

Upvotes: 1

Views: 381

Answers (3)

jpw
jpw

Reputation: 44891

Since the WebSQL database used for w3schools doesn't seem to support year() I think you could use this instead:

SELECT e.LastName, e.FirstName, SUM(od.Quantity*p.Price) AS OrderTotal
FROM [Employees] AS e 
JOIN [Orders] AS o ON o.EmployeeID=e.EmployeeID
JOIN [OrderDetails] AS od ON od.OrderID=o.OrderID
JOIN [Products] AS p ON od.ProductID=p.ProductID
WHERE OrderDate BETWEEN '1996-01-01' AND '1996-12-31'
GROUP BY e.EmployeeID
ORDER BY OrderTotal DESC
LIMIT 4
OFFSET 1

It seems to work when I tested with Chrome (but not Firefox)

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16690

You can use the YEAR() function. Check out this list of useful datetime functions.

Try this new query:

SELECT e.LastName, e.FirstName, SUM(od.Quantity*p.Price) AS OrderTotal
FROM [Employees] AS e 
JOIN [Orders] AS o ON o.EmployeeID=e.EmployeeID
JOIN [OrderDetails] AS od ON od.OrderID=o.OrderID
JOIN [Products] AS p ON od.ProductID=p.ProductID
WHERE YEAR([OrderDate]) = 1996
GROUP BY e.EmployeeID
ORDER BY OrderTotal DESC
LIMIT 4
OFFSET 1

Upvotes: 1

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

CODE:

WHERE DATEPART(year, [OrderDate])=1996

Upvotes: 0

Related Questions