SQL COUNT DISTINCT with CASE : How do i subquery without using exists?

Question

context: In SQL Server have a big XLSX file that i am mapping to an XML file. A column called 'referentie' gets different values each time. I need to count unique values and display a code accordingly . (Please note that is a subquery inside a bigger SELECT statement. I'm not able to add additional aggregate funcitons.)

1. I need to count the unique values of a JSON array input (Output is in XML). (done)
2. Display values depending on the retrieved unique count (issue)

issue : In the query you'll see i have multiple COUNT DISTINCT CASES. SQL throws error which is normal :

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My query :

(SELECT COUNT(DISTINCT CASE WHEN Referentie > 10 THEN N'CON1' END) AS [HEADER/CHECK],
                        COUNT(DISTINCT CASE WHEN Referentie <= 10 AND Referentie > 29 THEN N'CON2' END) AS [HEADER/CHECK],
                        COUNT(DISTINCT CASE WHEN Referentie <= 30 AND Referentie >= 50 THEN N'CON3' END) AS [HEADER/CHECK],
                        COUNT(DISTINCT CASE WHEN Referentie <= 50 AND Referentie >= 500 THEN N'CON4' END) AS [HEADER/CHECK]
                FROM OPENJSON(@Json, N'$.Blad1')
                WITH (Referentie nvarchar(100) N'$.Referentie')) AS [HEADER/CHECK],

Question:

• What is the correct syntax here to make my logic work ?

JSON in file :

@Json = N'{
   "Blad1":[
      {
         removed
      }
   ]
}'

Answer 1

Firstly, you are being somewhat unclear about what you are trying to achieve. You say you are managing to count unique values, but not display a value dependent on that.

Your code doesn't even manage that. What it actually does is check whether Referentie is within defined numbers (which makes no sense as it's a string), then distinct over a single string. Then it returns this multiple times, once for each parameter.

Instead, you need a single COUNT (DISTINCT Referentie), which has a CASE around it checking how much it is.

There is another issue with your current code. The conditions > and < are backwards. And < 29 should probably be < 30.

SELECT
  'SomeValue' AS xyz,
  (
    SELECT
      CASE 
        WHEN COUNT(DISTINCT Referentie) < 10
          THEN N'CON1'
        WHEN COUNT(DISTINCT Referentie) >= 10 AND COUNT(DISTINCT Referentie) < 29
          THEN N'CON2'
        WHEN COUNT(DISTINCT Referentie) >= 30 AND COUNT(DISTINCT Referentie) <= 50
          THEN N'CON3'
        WHEN COUNT(DISTINCT Referentie) >= 50 AND COUNT(DISTINCT Referentie) <= 500
          THEN N'CON4'
      END
    FROM OPENJSON(@Json, N'$.Blad1')
      WITH (
        Referentie nvarchar(100)
      ) AS j
  ) AS [HEADER/CHECK]
FROM (VALUES(0)) DummyTable(DummyValue)   -- don't know what the rest of your query is
FOR XML PATH(N''), ROOT(N'rootElement'),TYPE

db<>fiddle

As a side point, if you really wanted multiple columns from a subquery, you would need to place it in a APPLY.

Answer 2

I've put the first query in a CTE and then your conditions in the query following but the results are probably not what you are expecting. Firstly Referentie is alpha-numerique so I have just used the numeric part, secondly only one of the values you are testing is met.
I have therefore grouped by Referntie and shown the number of duplicates to make things clearer while you are refining your query.

declare @json varchar(max);
set @Json = ----   THE VALUE GIVEN IN THE QUESTION  ---

WITH myJson as (
select 
  right(Referentie,7) Referentie
FROM OPENJSON(@Json, N'$.Blad1')
 WITH (Referentie nvarchar(100) ) AS [HEADER/CHECK]
)
SELECT
  COUNT(*) "number",
  Referentie,
  COUNT(DISTINCT CASE WHEN Referentie > 10 THEN N'CON1' END) AS [HEADER/CHECK],
  COUNT(DISTINCT CASE WHEN Referentie <= 10 AND Referentie > 29 THEN N'CON2' END) AS [HEADER/CHECK],
  COUNT(DISTINCT CASE WHEN Referentie <= 30 AND Referentie >= 50 THEN N'CON3' END) AS [HEADER/CHECK],
  COUNT(DISTINCT CASE WHEN Referentie <= 50 AND Referentie >= 500 THEN N'CON4' END) AS [HEADER/CHECK]
FROM myJson
GROUP BY Referentie

GO

number | Referentie | HEADER/CHECK | HEADER/CHECK | HEADER/CHECK | HEADER/CHECK
-----: | :--------- | -----------: | -----------: | -----------: | -----------:
     1 | 2348657    |            1 |            0 |            0 |            0
    10 | 4221860    |            1 |            0 |            0 |            0
     1 | 6969860    |            1 |            0 |            0 |            0

db<>fiddle here