Jump to content
TNG Community
JPT

Report: Places by Reference Count

Recommended Posts

JPT

select 
	gedcom, 
	concat ('<a href="placesearch.php?psearch=',place, '&tree=', gedcom, '">', place, '</a>') as place, 
	count
from (
	select gedcom, place, count(place) as count from (
		select gedcom, birthplace as place
		from tng_people 
		where not birthplace is null and not birthplace like "" 
	union all
		select gedcom, deathplace as place
		from tng_people 
		where not deathplace is null and not deathplace like "" 
	union all
		select gedcom, marrplace as place 
		from tng_families 
		where not marrplace is null and not marrplace like ""    
	) as places group by gedcom, place 
) as result where count > 1 order by gedcom, count desc;

This report returns a reference count of places. References includes birth, death and marriage.

In the last line you can limit the count. As it is now, it will remove from the result all places that only have ONE reference.

If you find this useful, please tell me. Every now and then I share code that might be useful to others. but until now I never got any feedback...

 

 

 

Share this post


Link to post
Share on other sites
Chris Lloyd

Thanks will try it out.

Share this post


Link to post
Share on other sites
Kare

Can this code be expanded - so that I can see a report of the records counted by places, by clicking the numbers listed?

Share this post


Link to post
Share on other sites
JPT

Well.

Without your question i never would have had the idea.

it worked!

select gedcom, place, count,
concat (
'<a href="placesearch.php?psearch=',
place,'&tree=',gedcom, '">View Place</a>') as link
from (
	select gedcom, place, count(place) as count from (
		select gedcom, birthplace as place
		from tng_people 
		where not birthplace is null and not birthplace like "" 
	union all
		select gedcom, deathplace as place
		from tng_people 
		where not deathplace is null and not deathplace like "" 
	union all
		select gedcom, marrplace as place 
		from tng_families 
		where not marrplace is null and not marrplace like ""    
	) as places group by gedcom, place 
) as limited where count > 1 order by gedcom, count desc;

if you want it to open in a new tab/window add target="_blank" after <a

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×