SQL query for distinct count

Vamshi 151 Reputation points
2022-05-13T19:58:19.67+00:00

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

Azure SQL Database
Developer technologies Transact-SQL
SQL Server Other
0 comments No comments
{count} votes

Accepted answer
  1. Naomi Nosonovsky 8,431 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
    

5 additional answers

Sort by: Most helpful
  1. Viorel 122.5K 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.


  2. Naomi Nosonovsky 8,431 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
    

  3. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    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.


  4. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.