SQL Server Count of
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 co开发者_StackOverflow社区rrect 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.
SELECT
Order.OrderTypeID,
COUNT(Order.OrderTypeID) AS 'Count1',
COUNT(distinct Order.ProductID) as 'Count2'
FROM
Order
GROUP BY Order.OrderTypeID
Use DISTINCT:
SELECT
Order.OrderTypeID,
COUNT(Order.OrderTypeID) AS 'Count1'
COUNT(DISTINCT Order.OrderTypeID) AS 'Count2'
FROM
Order
GROUP BY Order.OrderTypeID
精彩评论