开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜