Count CustomersOrders

Assuming you have the CustomerID in the Orders table as a foreign key back to the Customers table, you can do a group by on the Orders.CustomerID and do a count. Using the Northwind database schema: select CustomerID, count(OrderID)from Ordersgroup by CustomerID This will only give you results for the customers that have orders. If you need the customers without orders as well, you will need to include the Customers table. For example with a left outer join: select C.CustomerID, count(O.OrderID)from Customers C left outer join Orders O on C.CustomerID=O.CustomerIDgroup by C.CustomerID Best regards Michael <Hassano@.discussions.microsoft.com> wrote in message news:d8b7a568-55d1-4bf8-a2f3-3a5635706ab4@.discussions.microsoft.com...Dear Team if there are three tables customers, Order and OrderDetails how to count the number of orders for each certain customer Thank you very muchDear Team
if there are three tables customers, Order and OrderDetails
how to count the number of orders for each certain customer

Thank you very much|||Mr NNTP
The Query looks good thank you very much
i will take some time to test it

Could not create a Foreign key on 2 unique columns

Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.in order for a compound FK to be declared, it must reference a compound unique key

in other words, it's not enough that ID1 and ID2 are each unique, the pair of them must be unique too|||The bigger questions are why do you have TWO unique columns in a single table, and why would you feel the need to have TWO foreign keys to the same unique record?|||Thank you for you reply.
Yes, I know that in MS Server documentation said those columns in FK must refer to A unique constraint.

My question was “why is not possible..” because mathematical it is possible to unique identify a row in table T_FK with a row in table T_PK.
I put such a question only from this logical point of view not for a need of use..
and maybe an answer could be you first question… Practically it’s no need to have 2 unique columns in the same table.|||why can a compound FK not reference two separately-unique-but-perhaps-invalid-when-combined keys?

becuase of the possibility that together, they might be invalid, i.e. not exist

a FK must refer to a unique row (this is relational integrity), and a unique row must be identifiable by a unique key (this is entity integrity)

if ID1 and ID2 are separately unique, then table1 could have rows like this --

ID1 ID2
101 205
102 206
103 207
104 208

your FK definition is not valid because it would allow {101,208} as a FK which doesn't exist as a PK, even though 101 and 208 do exist and are separately unique