February 13, 2011 at 2:42 pm
I'm working on a tutorial which ask the following:
Create a report that shows the total number of orders by Customer since December 31, 1996. The report should only return rows for which the NumOrders is greater than 15.
Here is my sql code. The only problem is that it groups by company name correctly but I cannot figure out how to count each order by the same company. Can anyone tell me what I'm missing. Is the overall structure correct or do I need a sub querry ?
select c.companyname, od.orderid, o.orderdate
from "order details" od
inner join orders o on o.orderid = od.orderid
inner join customers c on o.customerid = c.customerid
where o.orderdate > '19961231'
group by c.companyname, od.orderid, o.orderdate
Thanks in advance
February 13, 2011 at 4:05 pm
Had to use Northwind in my SQL 2008 but is this what you are looking for:
select c.companyname, COUNT(od.orderid) AS 'Number of orders' --==, o.orderdate
from "order details" od
inner join orders o on o.orderid = od.orderid
inner join customers c on o.customerid = c.customerid
where o.orderdate > '19961231'
group by c.companyname, od.orderid ORDER BY COUNT(od.orderid) DESC
The above returns only a single company that has more than 15 orders so may not be that comparable to the SQL 2005 version of Northwind. But hopefully is will get your thinking on track....
February 13, 2011 at 4:14 pm
Firstly thanks for the reply. Secondly after running the above code in SQL Serve 2005 it returns 678 rows. Does that sound correct ? The report should return 5 rows.
You can see the tutorial here http://www.learn-sql-tutorial.com/SubqueriesJoinsUnions.cfm#h1.3
February 13, 2011 at 4:22 pm
BE CAREFUL the T-SQL I posted will return 678 rows BUT that is all the rows and what you desire is ONLY those companies with the number of orders above 15. So you need a having clause or a subquery to retrieve the data that you specifically state you should have returned.
February 14, 2011 at 12:16 pm
eh936,
The only way I know of to get the order info from just customers with 15 or more orders is to first determine which customers have that many orders and then use that to get the actual order information. It's not real efficient since it has to touch upon the same rows twice.
This code should give you what you want. I couldn't test it since I don't have Nortwind.
;WITH CTECust AS
( SELECT O.CustomerID, C.CompanyName, COUNT(*) AS OrderCount
FROM Orders O
INNER JOIN Customers C ON
O.CustomerID = CustomerID
WHERE OrderDAte > '19961231'
GROUP BY CustomerID
HAVING COUNT(*) >= 15
)
SELECT CTE.CustomerID, CTE.CompanyName, O.OrderID, O.OrderDate
FROM CTECust CTE
INNER JOIN Oders O ON
CTE.CustomerID = O.CustomerID
WHERE
O.OrderDate > '19961231'
Todd Fifield
February 14, 2011 at 1:53 pm
tfifield (2/14/2011)
eh936,The only way I know of to get the order info from just customers with 15 or more orders is to first determine which customers have that many orders and then use that to get the actual order information. It's not real efficient since it has to touch upon the same rows twice.
This code should give you what you want. I couldn't test it since I don't have Nortwind.
;WITH CTECust AS
( SELECT O.CustomerID, C.CompanyName, COUNT(*) AS OrderCount
FROM Orders O
INNER JOIN Customers C ON
O.CustomerID = CustomerID
WHERE OrderDAte > '19961231'
GROUP BY CustomerID
HAVING COUNT(*) >= 15
)
SELECT CTE.CustomerID, CTE.CompanyName, O.OrderID, O.OrderDate
FROM CTECust CTE
INNER JOIN Oders O ON
CTE.CustomerID = O.CustomerID
WHERE
O.OrderDate > '19961231'
Todd Fifield
Thanks for the code I tried it out but gave me errors in SQL Server 2005. I'll keep working on it.
Appreciate the reply.
February 15, 2011 at 11:32 am
It probably gave you an ambiguous column name error since I forgot to specify a table alias in the join in the CTE.
INNER JOIN Customers C ON
O.CustomerID = CustomerID
Should be:
INNER JOIN Customers C ON
O.CustomerID = C.CustomerID
This should run fine in SQL 2005. Is your database compatibility 80 (SQL 2000) perhaps?
Todd Fifield
February 15, 2011 at 12:07 pm
tfifield (2/15/2011)
It probably gave you an ambiguous column name error since I forgot to specify a table alias in the join in the CTE.
INNER JOIN Customers C ON
O.CustomerID = CustomerID
Should be:
INNER JOIN Customers C ON
O.CustomerID = C.CustomerID
This should run fine in SQL 2005. Is your database compatibility 80 (SQL 2000) perhaps?
Todd Fifield
Thank you for the reply once again. I made the change you had suggested but go another error. I was able to resolve the query with the following code:
select c.companyname, count(*)
from orders o
inner join customers c on o.customerid = c.customerid
where o.orderdate > '19961231'
group by c.companyname
having count(*) > 15
February 15, 2011 at 12:22 pm
Thanks for letting me know. I thought your requirement was to list the actual orders for those customers. Of course your query works fine and is very simple if you only need a list of the customers.
I'm curious, however, as to the exact error you were getting.
Todd Fifield
February 15, 2011 at 1:23 pm
tfifield (2/15/2011)
Thanks for letting me know. I thought your requirement was to list the actual orders for those customers. Of course your query works fine and is very simple if you only need a list of the customers.I'm curious, however, as to the exact error you were getting.
Todd Fifield
After making the change you mentioned I got the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Orders'.
February 16, 2011 at 12:10 pm
eh936 (2/15/2011)
tfifield (2/15/2011)
Thanks for letting me know. I thought your requirement was to list the actual orders for those customers. Of course your query works fine and is very simple if you only need a list of the customers.I'm curious, however, as to the exact error you were getting.
Todd Fifield
After making the change you mentioned I got the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Orders'.
I can't see why you get this error. There is a table named Orders. Are you sure your compatibility level on the database is 90? If you try to use some of the new features in SQL 2005 on a SQL 2000 database (compatibility 80) then you can get some unexplained errors.
Todd Fifield
February 16, 2011 at 1:08 pm
tfifield (2/16/2011)
eh936 (2/15/2011)
tfifield (2/15/2011)
Thanks for letting me know. I thought your requirement was to list the actual orders for those customers. Of course your query works fine and is very simple if you only need a list of the customers.I'm curious, however, as to the exact error you were getting.
Todd Fifield
After making the change you mentioned I got the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Orders'.
I can't see why you get this error. There is a table named Orders. Are you sure your compatibility level on the database is 90? If you try to use some of the new features in SQL 2005 on a SQL 2000 database (compatibility 80) then you can get some unexplained errors.
Todd Fifield
I checked the compatibility level on SQL Server 2005 as follows:
sp_dbcmptlevel 'Northwind'
The current compatibility level is 90.
February 20, 2011 at 12:20 pm
eh936,
I'm curious as to exactly what the error message is then. I looked over my query and I can't see an error in it and I don't have Northwind so I can't test it. I may have missed something in the syntax.
Todd Fifield
February 23, 2011 at 6:37 pm
tfifield (2/20/2011)
eh936,I'm curious as to exactly what the error message is then. I looked over my query and I can't see an error in it and I don't have Northwind so I can't test it. I may have missed something in the syntax.
Todd Fifield
Here is the code with corrections (minor) that works. But it does not summarize the data the way the question that was asked of me. Thanks again for the help.
;WITH CTECust AS
( SELECT O.CustomerID, C.CompanyName, COUNT(*) AS OrderCount
FROM Orders O
INNER JOIN Customers C ON
O.CustomerID = C.CustomerID
WHERE OrderDAte > '19961231'
GROUP BY O.CustomerID, C.CompanyName
HAVING COUNT(*) >= 15
)
SELECT CTE.CustomerID, CTE.CompanyName, O.OrderID, O.OrderDate
FROM CTECust CTE
INNER JOIN Orders O ON
CTE.CustomerID = O.CustomerID
WHERE
O.OrderDate > '19961231'
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply