November 6, 2010 at 6:46 am
I have 2 tables Customers and Orders. I am trying to get a count of orders for each customer in the orders table and the following does that:
SELECT COUNT(Orders.Orderid), Orders.Custid
FROM Orders
GROUP BY Orders.CustID
But I want to see the customer's name in each row in addition to the customer id and the customer's name is in the Customers table. The following works
SELECT Count(Orders.orderid) AS CountOforderid, Orders.custid, Customers.Name1
FROM Orders INNER JOIN Customers ON Orders.CustID=Customers.CustID
GROUP BY Orders.custid, Customers.Name1
But you have to include the customers name in the GROUP BY clause or you get an error message. Is there any way to do it without putting Name1 in the GROUP BY clause?
November 6, 2010 at 11:55 am
There are, but they require subqueries. Why are you concerned about having the name in the group by?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2010 at 7:19 pm
Because I want to add address. phone #, etc, and I know there's a way to do this I just can't figure it out.
November 7, 2010 at 2:45 am
Either all in the group by, or have a subquery (in the from clause) that queries and aggregates Orders and joins that to Customers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 2:16 pm
Thank you.
November 11, 2010 at 9:20 am
Try this:
SELECT A.Nbr_Orders,
A.CustID,
Customers.Name1
FROM
(SELECT COUNT(Orders.Orderid) as Nbr_Orders,
Orders.Custid as CustID
FROM Orders
GROUP BY Orders.CustID ) as A
INNER JOIN Customers ON A.CustID = Customers.CustID
November 11, 2010 at 2:07 pm
Very good. Thank you.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply