SQL query for distinct count

Question

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    

Answer 1

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

Answer 2

Hi @Vamshi
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.