SQL Server Count of

Question

I am trying to retrieve a count of orders for each order type (OrderTypeID) and a count of orders for each order type of distinct products (productID) eg:

OrderID, OrderTypeID, ProductID
1, 1, 1
2, 1, 6
3, 2, 6
4, 1, 1
5, 2, 6
6, 2, 6
7, 2, 6
8, 3, 1

Result:

OrderTypeID, Count1, Count2
1, 3, 2
2, 4, 1
3, 1, 1

I am currently retrieving count1 currently but have been unable to retrieve the correct result for count2.

A simplified version of my query is:

SELECT
    Order.OrderTypeID,
    COUNT(Order.OrderTypeID) AS 'Count1'
FROM 
    Order
GROUP BY Order.OrderTypeID

Greatly appreciate any assistance.

Answer 1

Use DISTINCT:

SELECT
    Order.OrderTypeID,
    COUNT(Order.OrderTypeID) AS 'Count1'
    COUNT(DISTINCT Order.OrderTypeID) AS 'Count2'
FROM 
    Order
GROUP BY Order.OrderTypeID

Answer 2

SELECT
    Order.OrderTypeID,
    COUNT(Order.OrderTypeID) AS 'Count1',
    COUNT(distinct Order.ProductID) as 'Count2'

FROM 
    Order
GROUP BY Order.OrderTypeID