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

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 

Answer 1

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

Answer 2

User-471420332 posted
the answer is not help full..

Answer 3

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?

Answer 4

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

Answer 5

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.

Answer 6

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