SQL query for distinct count

Vamshi 151

Hi Support team, I need help in correcting my mistake in the query.

Scenario 1:

The below query is working as expected based on the provided ANUMBER MLSR12677 and it is returning COUNT as 1 which is right result. Please refer the attached screenshot.

   SELECT IFNULL((SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN   
   WHERE WN.ANUMBER IN ('MLSR12677')    
   AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC   
   WHERE IC.UNITNAME = 'MAS')    
   GROUP BY WN.ANUMBER, WN.DEPTKEY), 0) AS COUNT

Scenario 2:

The below query is working as expected based on the provided ANUMBER S448904 and it is returning COUNT as 2 which is right result. Please refer the attached screenshot.

   SELECT IFNULL((SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN   
   WHERE WN.ANUMBER IN ('S448904')    
   AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC   
   WHERE IC.UNITNAME = 'MAS')    
   GROUP BY WN.ANUMBER, WN.DEPTKEY), 0) AS COUNT

Issue in the Scenario 3:

When i provide both ANUMBERS in the below query, i see a error message returned 'Single-row subquery returns more than one row.' Please refer the attached screenshot.

I WANT the result to be returned as COUNT as 2. Please provide inputs to solve it. Thank you.

   SELECT IFNULL((SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN   
   WHERE WN.ANUMBER IN ('MLSR12677','S448904')   
   AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC   
   WHERE IC.UNITNAME = 'MAS')    
   GROUP BY WN.ANUMBER, WN.DEPTKEY), 0) AS COUNT

Accepted answer

Naomi 7,361 Reputation points

2022-05-15T17:12:37.507+00:00
May be:

select max(count) as HighestCount from ( SELECT COUNT(DISTINCT WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('MLSR12677','S448904') AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS') GROUP BY WN.DEPTKEY, ANumber) X
Please sign in to rate this answer.
Vamshi 151 Reputation points

2022-05-17T03:08:57.043+00:00

Thank you @Naomi for the inputs. The updated query you shared works as per my needs.

I did modify the query by adding a IFNULL condition to return COUNT as 0 when it is NULL.

SELECT IFNULL ((select max(count) as HighestCount from ( SELECT COUNT(DISTINCT WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN
WHERE WN.ANUMBER IN ('MLSR12677','S448904')
AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC
WHERE IC.UNITNAME = 'MAS')
GROUP BY WN.DEPTKEY, ANumber) X ), 0) AS COUNT

Erland Sommarskog 100.9K Reputation points MVP

2022-05-17T21:15:34.827+00:00

I did modify the query by adding a IFNULL condition to return COUNT as 0 when it is NULL.

Although, COUNT() always returns a non-NULL value.
Sign in to comment

5 additional answers

Viorel 112.1K Reputation points

2022-05-13T20:04:43.877+00:00
Probably

SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('MLSR12677','S448904') AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS') GROUP BY WN.ANUMBER, WN.DEPTKEY

returns several rows (counts).

ISNULL and IFNULL cannot be used in this case.
Please sign in to rate this answer.
Vamshi 151 Reputation points

2022-05-13T20:27:18.737+00:00

Thank you for the inputs Viorel .

I have executed the query you shared and it is returning 2 rows. But is there a way to return one row (i.e. one record) always with COUNT as 2 when there is a combination of multiple ANUMBERs provided. Attached the result screenshot for reference.
Sign in to comment
Naomi 7,361 Reputation points

2022-05-13T20:33:50.647+00:00
Are you looking for:

SELECT COUNT(DISTINCT WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('MLSR12677','S448904') AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS') GROUP BY WN.DEPTKEY
Please sign in to rate this answer.
Vamshi 151 Reputation points

2022-05-13T20:45:05.067+00:00

Thank you Naomi.

I have executed the query you shared and it is returning 2 rows. I am looking to return one row (i.e. one record) in this scenario with COUNT result as 2 when there is a combination of multiple ANUMBERs provided.

I am looking to return the result like below in the screenshot:

Erland Sommarskog 100.9K Reputation points MVP

2022-05-13T20:57:57.097+00:00

And can you explain why 2 would be the correct result? Again, I remind you that we don't know your tables or data, so what may be obvious to you is not obvious to us.

Naomi 7,361 Reputation points

2022-05-13T22:35:05.797+00:00

What will happen if you remove GROUP BY clause? I think you'll get 2 as you want.
Sign in to comment
Erland Sommarskog 100.9K Reputation points MVP

2022-05-13T20:40:09.337+00:00

First of all, we don't know your tables or your data, so we don't know why you think 2 would be a correct result.

From where I sit, I need to say that none of the queries makes much sense to me.

You have SELECT DISTINCT COUNT(*) ... GROUP BY col1, col2. Such a query will most of the time produce more than one row, since the number of rows for each combination of col1 and col2 is likely to be different. Also, it's somewhat strange to only see the counts being returned, but not the column values.

It could certainly help if you gave the background to these queries and explained what you are trying to achieve.
Please sign in to rate this answer.
Vamshi 151 Reputation points

2022-05-13T21:28:19.77+00:00

Hi @Erland Sommarskog , Thank you for your questions.
Below is the view DEV.DBO.VW_NUMBER data and fyi.. I am joining view DEV.DBO.VW_DATA IC to link the UNITKEY.

When i search the ANUMBER S448904 in the view, it returned 2 APPKEY (i.e. 2 rows) based on the combination of ANUMBER, DEPTKEY, UNITKEY. The Count is 2. If Count is greater than 1 ( i.e. 2 ) then there is a functional logic in the Service application where we are restricting to further process and then we are displaying an alert message.

Please refer the screenshot below.

![201796-image.png][1]

When i search the ANUMBER MLSR12677 in the table, it returned 1 APPKEY (i.e. 1 row) based on the combination of ANUMBER, DEPTKEY, UNITKEY. The Count is 1 here.
Please refer the screenshot below.

![201797-image.png][2]

In the below query, when both ANUMBERs S448904 and MLSR12677 are provided which are belonging to same UNIT , I am trying to see if there is a way to return one row (i.e. one record) with COUNT result as 2 so that my functional logic works as is. ( i.e. If Count is greater than 1 ( i.e. 2 ) then there is a functional logic in the Service application where we are restricting to further process and then we are displaying an alert message. )

SELECT COUNT(DISTINCT WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('MLSR12677','S448904') AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS') GROUP BY WN.DEPTKEY

Vamshi 151 Reputation points

2022-05-13T21:33:29.223+00:00

Hi @Erland Sommarskog , I missed to attach the screenshots in the previous POST.

Below is the view DEV.DBO.VW_NUMBER data and fyi.. I am joining view DEV.DBO.VW_DATA IC to link the UNITKEY.

When i search the ANUMBER S448904 in the view, it returned 2 APPKEY (i.e. 2 rows) based on the combination of ANUMBER, DEPTKEY, UNITKEY. The Count is 2. If Count is greater than 1 ( i.e. 2 ) then there is a functional logic in the Service application where we are restricting to further process and then we are displaying an alert message. Please refer the screenshot below.

When i search the ANUMBER MLSR12677 in the table, it returned 1 APPKEY (i.e. 1 row) based on the combination of ANUMBER, DEPTKEY, UNITKEY. The Count is 1 here. Please refer the screenshot below.
Sign in to comment
Erland Sommarskog 100.9K Reputation points MVP

2022-05-14T08:41:06.53+00:00
So as Naomi suggests, it may be as simple as this:

SELECT COUNT(*) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('MLSR12677','S448904') AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')

From your description with the screenshots, I can't see why you need that GROUP BY.
Please sign in to rate this answer.
Vamshi 151 Reputation points

2022-05-15T15:14:31.417+00:00

Thank you @Erland Sommarskog for sharing the updated query. My intention in using GROUP BY is to return the distinct APPKEY records based on the combination of ANUMBER, DEPTKEY in a UNIT (i.e. used UNITKEY). Correct me if my understanding is wrong.

Need inputs on the below scenarios. Is it possible to return the distinct highest Count value. If Count is greater than 1 ( i.e. 2 or 3 or 4 ) then there is a functional logic in the Service application side where we are restricting to further process and then we are displaying an alert message about the COUNT reason. Refer the screenshots below.

if the results come up like below, The query should return one record with the COUNT as 2. Basically, I want the query to return the distinct highest Count value.

if the results come up like below, The query should return one record with the COUNT as 2. Basically, I want the query to return the distinct highest Count value.

if the results come up like below, The query should return one record with the COUNT as 3. Basically, I want the query to return the distinct highest Count value.

if the results come up like below, The query should return one record with the COUNT as 4. Basically, I want the query to return the distinct highest Count value.

Erland Sommarskog 100.9K Reputation points MVP

2022-05-15T18:52:35.227+00:00

Thank you @Erland Sommarskog for sharing the updated query. My intention in using GROUP BY is to return the distinct APPKEY records based on the combination of ANUMBER, DEPTKEY in a UNIT (i.e. used UNITKEY). Correct me if my understanding is wrong.

Well, I don't know much about your actual business problem, so I cannot say whether your understanding is wrong or right. But if your understanding is incorrect, the queries that we will give you are likely to be incorrect as well.

As for a query given your explanation above, it seems that Naomi beat me to it.

Vamshi 151 Reputation points

2022-05-17T03:10:35.813+00:00

Thank you @Erland Sommarskog . Appreciate your inputs.
Sign in to comment