I have two tables in DB Orders with columns Orders Id, Orders NO, Customer ID; Customer with columns Customer ID, Customer email. I need a count of the orders for each customer and group by email with output something like
SELECT Customer.Customer Email,
Count Orders.Order ID,
From Orders,
Left Join Orders on Customer.Customer ID
Group by Customer.Customer email, Orders.Order ID
You're kind of close by using a LEFT JOIN but you should start with your Customer table like this:
SELECT Customer.[Customer Email], COUNT(Orders.[Order ID]) AS OrderCount,
FROM Customer,
LEFT JOIN Orders
ON Customer.[Customer ID] = Orders.[Customer ID]
GROUP BY Customer.[Customer Email]
Note this assumes the Customer Email field is unique in the Customer table, otherwise if two Customers can share the same email, then you should group on Customer ID instead. Also you should avoid spaces in your columns names, it's best practice to not use special characters.
Additional note, an edit suggestion was made to use COUNT(DISTINCT Orders.[Order ID]) instead, on the query I suggested, in case Order ID was not unique in the Orders table. While it's a good thought, it's also an assumption (and likely not the case in this context). Using extra operators like DISTINCT (when not needed) could lead to alternative execution plans that are potentially less performant (e.g. DISTINCT will likely result in an additional sort operation to be performed). Again I think it's a good thought, but I don't like to make answers on assumptions when there are other potential consequences, so I think the query I proposed is sufficient unless OP provides any additional information that states otherwise.
