SQL Group By for two tables

I currently have two tables, one with customer information and the other with order information. I am trying to put together a merged table that gives me the customer info and adds a column that counts total orders.

I am currently writing my query similar like this:

SELECT 
    C.PrimaryKey,
    C.ClientNumber,
    COUNT(O.Orders) AS 'Number of Orders'
FROM
    ClientInfo C
JOIN 
    Orders O ON O.PrimaryKey=C.PrimaryKey
GROUP BY 
    C.ClientNumber

I have ran different variances of the above query and get 1 of 2 results;

• An error 'is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'
• Or an incorrect output that counts each order as its own row instead of counting totals in one column:

Ideally, my output would look like this:

In trying to covert your original query to a simple sample query, you have mutilated it. It makes no sense to join two tables an their respective primary key. You want to join on a foreign key instead.

It seems you want to count orders per client. I suggest you count your orders per client and join this to the client table. Thus you can show all columns of the client table. (The same should be possible by joining both tables and grouping by the client table's primary key, but SQL Server doesn't support this standard SQL feature, so you would have to put all client info columns you want to select in the GROUP BY clause, if you joined first and aggregated then.)

SELECT 
  c.clientinfo_id,
  c.clientnumber,
  o.number_of_orders
FROM clientinfo c
JOIN 
(
  SELECT clientinfo_id, COUNT(orders) AS number_of_orders
  FROM orders
  GROUP BY clientinfo_id
) o ON o.clientinfo_id = c.clientinfo_id
ORDER BY c.clientinfo_id;

Assuming your FK relation is Order.ForeignKey = ClientInfo.PrimaryKey, here is an example of working query:

SELECT 
  C.PrimaryKey
, C.ClientNumber,
, COALESCE(O.Count,0) AS [Number of Orders]
FROM ClientInfo C
LEFT JOIN 
(
   SELECT 
     O.ForeignKey
   , COUNT(*) AS Count
   FROM Orders O 
   GROUP BY O.ForeignKey
) O ON O.ForeginKey = C.PrimaryKey

Your question/query is unclear, so this suggested query is based on muscle memory:

SELECT 
  C.PrimaryKey,
  C.ClientNumber,
  COUNT(O.Orders) AS 'Number of Orders'
FROM ClientInfo C
JOIN Orders O ON O.ClientInfoPrimaryKey=C.PrimaryKey
GROUP BY C.PrimaryKey, C.ClientNumber

The changes are:

1. change O.PrimaryKey=C.PrimaryKey to O.ClientInfoPrimaryKey=C.PrimaryKey. You haven't told us what the name of the foreign key to customer in the orders table is, so I guessed it might be ClientInfoPrimaryKey - correct as necessary.

2. changed to group by C.PrimaryKey, C.ClientNumber - all non-aggregated columns