Asked by:
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
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