How to Count Distinct on Case When?

Question

I have been building up a query today and I have got stuck. I have two unique Ids that identify if and order is Internal or Web. I have been able to split this out so it does the count of how many times they appear but unfortunately it is not providing me with the intended result. From research I have tried creating a Count Distinct Case When statement to provide me with the results.

Please see below where I have broken down what it is doing and how I expect it to be.

Original data looks like:

Company Name       Order Date       Order Items      Orders     Value      REF
-------------------------------------------------------------------------------
CompanyA           03/01/2019        Item1           Order1      170       INT1
CompanyA           03/01/2019        Item2           Order1      0         INT1
CompanyA           03/01/2019        Item3           Order2      160       WEB2
CompanyA           03/01/2019        Item4           Order2      0         WEB2

How I expect it to be:

Company Name       Order Date       Order Items      Orders     Value      WEB       INT
-----------------------------------------------------------------------------------------
CompanyA           03/01/2019            4             2         330        1         1

What currently comes out

Company Name       Order Date       Order Items      Orders     Value      WEB       INT
 -----------------------------------------------------------------------------------------
 CompanyA           03/01/2019            4             2         330        2         2

As you can see from my current result it is counting every line even though it is the same reference. Now it is not a hard and fast rule that it is always doubled up. This is why I think I need a Count Distinct Case When. Below is my query I am currently using. This pull from a Progress V10 ODBC that I connect through Excel. Unfortunately I do not have SSMS and Microsoft Query is just useless.

My Current SQL:

SELECT 

Company_0.CoaCompanyName
, SopOrder_0.SooOrderDate
, Count(DISTINCT SopOrder_0.SooOrderNumber) AS 'Orders'
, SUM(CASE WHEN SopOrder_0.SooOrderNumber IS NOT NULL THEN 1 ELSE 0 END) AS 'Order Items'
, SUM(SopOrderItem_0.SoiValue) AS 'Order Value'
, SUM(CASE WHEN SopOrder_0.SooParentOrderReference LIKE 'INT%' THEN 1 ELSE 0 END) AS 'INT'
, SUM(CASE WHEN SopOrder_0.SooParentOrderReference LIKE 'WEB%' THEN 1 ELSE 0 END) AS 'WEB'

FROM 

SBS.PUB.Company Company_0
, SBS.PUB.SopOrder SopOrder_0
, SBS.PUB.SopOrderItem SopOrderItem_0

WHERE 

SopOrder_0.SopOrderID = SopOrderItem_0.SopOrderID 
AND Company_0.CompanyID = SopOrder_0.CompanyID
AND SopOrder_0.SooOrderDate > '2019-01-01'

GROUP BY 

Company_0.CoaCompanyName
, SopOrder_0.SooOrderDate

I have tried using the following line but it errors on me when importing:

, Count(DISTINCT CASE WHEN SopOrder_0.SooParentOrderReference LIKE 'INT%' THEN  SopOrder_0.SooParentOrderReference ELSE 0 END) AS 'INT'

Just so know the error I get when importing at the moment is syntax error at or about "CASE WHEN sopOrder_0.SooParentOrderRefer" (10713)

Answer 1

Try removing the ELSE:

COUNT(DISTINCT CASE WHEN SopOrder_0.SooParentOrderReference LIKE 'INT%' THEN  SopOrder_0.SooParentOrderReference END) AS num_int

You don't specify the error, but the problem is probably that the THEN is returning a string and the ELSE a number -- so there is an attempt to convert the string values to a number.

Also, learn to use proper, explicit, standard JOIN syntax. Simple rule: Never use commas in the FROM clause.

Answer 2

count distinct on the SooOrderNumber or the SooParentOrderReference, whichever makes more sense for you.

If you are COUNTing, you need to make NULL the thing that your are not counting. I prefer to include an else in the case because it is more consistent and complete.

, Count(DISTINCT CASE WHEN SopOrder_0.SooParentOrderReference LIKE 'INT%' THEN  SopOrder_0.SooParentOrderReference ELSE null END) AS 'INT'

Gordon Linoff is correct regarding the source of your error, i.e. datatype mismatch between the case then value else value end. null removes (should remove) this ambiguity - I'd need to double check.

Editing my earlier answer...

Even though it looks, as you say, like count distinct is not supported in Pervasive PSQL, CTEs are supported. So you can do something like...

This is what you are trying to do but it is not supported... with dups as ( select 1 as id, 'A' as col1 union all select 1, 'A' union all select 1, 'B' union all select 2, 'B' ) select id ,count(distinct col1) as col_count from dups group by id;

Stick another CTE in the query to de-duplicate the data first. Then count as normal. That should work...

with
dups as
(
select 1 as id, 'A' as col1 union all select 1, 'A' union all select 1, 'B' union all select 2, 'B'
)
,de_dup as
(
select id
      ,col1
  from dups
group by id
        ,col1
)
select id
      ,count(col1) as col_count
  from de_dup
group by id;

These 2 versions should give the same result set.

There is always a way!!

Answer 3

I cannot explain the error you are getting. You are mistakenly using single quotes for alias names, but I don't actually think this is causing the error.

Anyway, I suggest you aggregate your order items per order first and only join then:

SELECT
  c.coacompanyname
, so.sooorderdate
, COUNT(*) AS orders
, SUM(soi.itemcount) AS order_items
, SUM(soi.ordervalue) AS order_value
, COUNT(CASE WHEN so.sooparentorderreference LIKE 'INT%' THEN 1 END) AS int
, COUNT(CASE WHEN so.sooparentorderreference LIKE 'WEB%' THEN 1 END) AS web
FROM sbs.pub.company c
JOIN sbs.pub.soporder so ON so.companyid = c.companyid
JOIN 
(
  SELECT soporderid, COUNT(*) AS itemcount, SUM(soivalue) AS ordervalue
  FROM sbs.pub.soporderitem
  GROUP BY soporderid
) soi ON soi.soporderid = so.soporderid
GROUP BY c.coacompanyname, so.sooorderdate
ORDER BY c.coacompanyname, so.sooorderdate;