CODEWITHSUNDEEP
sqlcountdistinctcase
Kurt Wagner
Kurt Wagner

Reputation: 3315

How To Get A Distinct Count Using a Nested Case Query?

I have an issue somewhat like:

MySQL: Sum values in subqueries

where I have a large where section that does some filtering and then want to do filtering in the select section when I'm doing different categories.

My situation is different in that I need to use a count distinct using case and not a sum: You have multiple orders and want to count the number of orders for a particular product (in addition to returning a bunch of other aggregates based on other conditions). However, due to technological issues, sometimes the order system will create two nearly identical rows that have the same ProductID and same OrderID (other columns will be slightly different such as time of order). However, you only want to count the number of distinct orders (eg only +1 for each unique OrderID) for a particular product. However for some other aggregates you need to use the duplicate entry, so you have to use a case subquery for count in the select section.

I created a query that almost works for this sample database:

SELECT COUNT(ProductID)
, COUNT(CASE WHEN ProductID = 51 THEN DISTINCT OrderDetailID END) 
FROM OrderDetails

However it complains about a syntax error. As soon as I remove 'DISTINCT' it works fine, but this causes duplicates to be counted (which while they don't exist in the sample database, exist in mine) which is why I need 'DISTINCT' to work

Upvotes: 1

Views: 3503

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Your query is quite close. You just need to move the distinct before the case:

SELECT COUNT(ProductID), 
       COUNT(DISTINCT CASE WHEN ProductID = 51 THEN OrderDetailID END) 
FROM OrderDetails

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

I would try something like this

select count(productid)
, case when productid = 51 then count(distinct orderdetailid)
else some other number end
from orderdetails

Upvotes: 0

Related Questions