locked
Sql query for find alert counts from two tables base values and fields name RRS feed

  • Question

  • User-471420332 posted

    I want to count the alerts of the cadidates base on district wise

    Below are District wise alert lookup table

    Table_LKP_AlertMastInfo
    
    DistrictID             FieldName              AlertOptionValue  
      71                    AreYouMarried                 Yes
      71                      Gender                      Female
      72                    AreYouMarried                 Yes

    Above Table_LKP_AlertMastInfo FieldName should compare with Table_RegistrationInfo fields to check the AlertOptionValue to get counts

    Below are candidate details table

    Table_RegistrationInfo
    
    CandidateId    DistrictID     AreYouMarried     Gender  
     Can001            71             Yes            Female
     Can002            71             No             Female
     Can003            72             Yes            Man  
     Can004            72             No             Man    

    I want output like below

    Can001   2
    Can002   1
    Can003   1

    explaination of above output counts

    Can001 have selected AreYouMarried:Yes and Gender:Female then count value 2
    Can002 have selected  Gender:Female then count value   1
    Can003 have selected AreYouMarried:Yes then count value   1
    Can004 have not alerts 
    Thursday, March 28, 2019 1:50 PM

All replies

  • User475983607 posted

    Basic SQL syntax.

    SELECT CandidateId, COUNT(DistrictID) AS [Count]
    FROM Table_RegistrationInfo
    GROUP BY CandidateId, DistrictID

    Reference docs

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017

    Thursday, March 28, 2019 5:13 PM
  • User-471420332 posted
    the answer is not help full..
    Thursday, March 28, 2019 7:37 PM
  • User475983607 posted

    mazhar khan india

    the answer is not help full..

    The query works.  The issue is you have not clearly explained why Can004 has no alerts.

    I want to count the alerts of the cadidates base on district wise
    Below are District wise alert lookup table
    Table_LKP_AlertMastInfo
    
    DistrictID             FieldName              AlertOptionValue  
      71                    AreYouMarried                 Yes
      71                      Gender                      Female
      72                    AreYouMarried                 Yes

    Above Table_LKP_AlertMastInfo FieldName should compare with Table_RegistrationInfo fields to check the AlertOptionValue to get counts

    Below are candidate details table

    Table_RegistrationInfo
    
    CandidateId    DistrictID     AreYouMarried     Gender  
     Can001            71             Yes            Female
     Can002            71             No             Female
     Can003            72             Yes            Man  
     Can004            72             No             Man    

    I want output like below

    Can001   2
    Can002   1
    Can003   1

    explaination of above output counts

    Can001 have selected AreYouMarried:Yes and Gender:Female then count value 2
    Can002 have selected  Gender:Female then count value   1
    Can003 have selected AreYouMarried:Yes then count value   1
    Can004 have not alerts 

    Is there any way you can show us the code that you have tried up to this point?

    Thursday, March 28, 2019 8:20 PM
  • User-893317190 posted

    Hi mazhar khan india,

    Since you only have two fields, you could try the sql below.

       with cte as (
       
       
        select  candidateId , districtId , (  
       
        select count(*) from Table_LKP_AlertMastInfo   -- select alert count from Table_LKP_AlertMastInfo
       
        where districtId =r.districtId  and  
    	
    	(
    	 (FieldName='AreYouMarried' and AlertOptionValue = r.AreYouMarried)  -- match AreYouMarried
    	
    	 or 
    
    	 (fieldName='Gender' and AlertOptionValue=r.Gender) )   -- or match Gender
    	 
    	 ) as [count] 
       
       from Table_RegistrationInfo  r
    
       ) 
       select * from cte where [count]!=0   -- filter out record whose count is zero

    The result.

    If you don't want to filter out  zero alert , you just write

      select  candidateId , districtId , (  
       
        select count(*) from Table_LKP_AlertMastInfo   -- select alert count from Table_LKP_AlertMastInfo
       
        where districtId =r.districtId  and  
    	
    	(
    	 (FieldName='AreYouMarried' and AlertOptionValue = r.AreYouMarried)  -- match AreYouMarried
    	
    	 or 
    
    	 (fieldName='Gender' and AlertOptionValue=r.Gender) )   -- or match Gender
    	 
    	 ) as [count] 
       
       from Table_RegistrationInfo  r

    Best regards,

    Ackerly Xu

    Friday, March 29, 2019 3:27 AM
  • User-471420332 posted

    Can004 of Table_RegistrationInfo match with Table_LKP_AlertMastInfo base on DistricID 

    So we will get data from Table_LKP_AlertMastInfo as below

    72  AreYouMarried  Yes

    So in Table_RegistrationInfo   Can004 field AreYouMarried  : NO is there but in Table_LKP_AlertMastInfo AreYouMarried : Yes so both are not match thats why hope you understand.

    Friday, March 29, 2019 5:37 AM
  • User-893317190 posted

    Hi mazhar khan india,

    I understand what you mean , because  Can004 field AreYouMarried  : NO and able_LKP_AlertMastInfo AreYouMarried : Yes  so they don't match each other.

    And in my sql , I also filter out Can004, you could see it from you  capture.

    What I mean is if you want to leave Can004  with count 0 , you could  use the second query, because record of every candidate  may be needed.

    If you don't want candidate who doesn't match alerts , you could use the first query.

    Best regards,

    Ackerly Xu

    Friday, March 29, 2019 7:08 AM