question

Vamshi-5948 avatar image
0 Votes"
Vamshi-5948 asked ErlandSommarskog commented

SQL query for distinct count

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

201850-image.png


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  

201902-image.png


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  


201836-image.png



sql-server-generalsql-server-transact-sqlazure-sql-database
image.png (87.8 KiB)
image.png (93.6 KiB)
image.png (95.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered ErlandSommarskog commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Thank you @NaomiNNN 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

0 Votes 0 ·

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.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Vamshi-5948 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.



201779-image.png


0 Votes 0 ·
image.png (46.7 KiB)
NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN commented

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
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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:

201885-image.png


0 Votes 0 ·
image.png (7.8 KiB)

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.

0 Votes 0 ·

What will happen if you remove GROUP BY clause? I think you'll get 2 as you want.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Vamshi-5948 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ErlandSommarskog, 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

0 Votes 0 ·

Hi @ErlandSommarskog, 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.
201839-image.png

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.
201926-image.png


0 Votes 0 ·
image.png (68.6 KiB)
image.png (62.5 KiB)
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Vamshi-5948 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @ErlandSommarskog 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.
202024-image.png
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.
202082-image.png
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.
202064-image.png
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.
202025-image.png


0 Votes 0 ·
image.png (2.0 KiB)
image.png (2.3 KiB)
image.png (2.2 KiB)
image.png (2.1 KiB)

Thank you @ErlandSommarskog 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.

0 Votes 0 ·
Vamshi-5948 avatar image Vamshi-5948 ErlandSommarskog ·

Thank you @ErlandSommarskog. Appreciate your inputs.

0 Votes 0 ·
LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered Vamshi-5948 commented

Hi @Vamshi-5948
It seams you need MAX(COUNT) to calculate the final COUNT.
Check this untested query:

 SELECT MAX(COUNT) AS COUNT FROM
 (
 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
 )T

Best regards,
LiHong


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @LiHongMSFT-3908 . Appreciate your time and query inputs

0 Votes 0 ·