Require help in Writing Query

Question

The following image have been uploaded to show what I am trying to do and what I wanted out of it

Can any one help me write the Query to get the results what I want Please check the following

SELECT * 
 FROM KPT 
 WHERE PROPERTY_ID IN (SELECT PROPERTY_ID 
                         FROM khata_header 
                        WHERE DIV_ID = 3 
                          and RECORD_STATUS = 0) 
   and CHALLAN_NO > 42646

The above is the query I have written and I have got the following result set

ID     CHALLAN_NO     PROPERTY_ID     SITE_NO              TOTAL_AMOUNT    
-----  -------------  --------------  -------------------  --------------- 
1242   42757          3103010141      296                  595             
1243   63743          3204190257      483                  594             
1244   63743          3204190257      483                  594             
1334   43395          3217010223      1088                 576             
1421   524210         3320050416      (null)               (null)          
1422   524210         3320050416      (null)               (null)          
1560   564355         3320021408      (null)               (null)          
1870   516292         3320040420      (null)               (null)          
1940   68357          3217100104      139                  1153            
1941   68357          3217100104      139                  1153            
2002   56256          3320100733      511                  4430            
2003   56256          3320100733      511                  4430            
2004   66488          3217040869      293                  3094            
2005   66488          3217040869      293                  3094            
2016   64571          3217040374      (null)               (null)          
2036   523122         3320020352      (null)               (null)          
2039   65682          3217040021      273                  919      

In my resultset, I am getting the PropertyId repeated, since there are multilple entries,

1. How Can I know How many have been repeated
2. What are those Property Id which have repeated more than 2 times.

Little Back ground about the tables are

1. PROPERTY_ID is the FK in the KPT
2. PROPERTY_ID is the PK in KH

I am writing a subquery to get the Result, so I am stuck I dont know how to get my results Please help

Answer 1

Does this get you any closer?

SELECT
  PROPERTY_ID, COUNT(*)
FROM
  KPT 
WHERE
  PROPERTY_ID IN (SELECT PROPERTY_ID 
                         FROM khata_header 
                         WHERE DIV_ID = 3 AND RECORD_STATUS = 0) 
  AND CHALLAN_NO > 42646
GROUP BY
  PROPERTY_ID
HAVING
  COUNT(*) >= 2

Answer 2

To determine which property ids have been repeated, the following untested query should work.

SELECT PROPERTY_ID, COUNT(PROPERTY_ID) AS PROPERTY_COUNT 
FROM KPT 
WHERE PROPERTY_ID IN (SELECT PROPERTY_ID 
                      FROM khata_header 
                      WHERE DIV_ID = 3 
                      AND RECORD_STATUS = 0) 
AND CHALLAN_NO > 42646
GROUP BY PROPERTY_ID

To determine which property ids have been repeated more than two times, add the following HAVING clause to the end of the above query.

HAVING COUNT(PROPERTY_ID) > 2

Answer 3

Maybe you can try this

The PROPERTY_ID repeat count can be achieved by an count() and a group by, to obtain the PROPERTY_ID's repeated more than 2 times you can create the first select into a temp table and create 2 select's on the temp table, at the end drop the temp table.

--create temp table

select PROPERTY_ID , count(*) as hitcount
into #tmp
from kpt
WHERE PROPERTY_ID IN (SELECT PROPERTY_ID 
                         FROM khata_header 
                        WHERE DIV_ID = 3 
                          and RECORD_STATUS = 0) 
   and CHALLAN_NO > 42646
group by PROPERTY_ID 

-- question 1:

select count(*) from #tmp where hitcount > 1

--and

union all

-- question 2:

select * from #tmp where hitcount > 2

drop table #tmp

Hopefully this answers your question.

Good luck