Jump to content
TNG Community
Appelmus

reports II

Recommended Posts

Appelmus

Hello!

This query from Henny Savenije's Report catalog provides a list of professions and their number.

SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY total DESC, Occupation;

Is anyone able to change the query so that occupations and numbers will be spent in a given time frame (z.B.1800-1820)?

Best regards

Appelmus

Share this post


Link to post
Share on other sites
Scott_Pryer

Here is the query. See underlined text for the additional WHERE statement. You can change the two numbers as needed.

SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" and birthdate BETWEEN 1800 AND 1820 GROUP BY Occupation ORDER BY total DESC, Occupation;

Share this post


Link to post
Share on other sites
Appelmus

Thank you, Scott!

Unfortunately it doesn't work.

"Abfrage: SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE et.tag="OCCU" AND info<>"" and birthdate BETWEEN 1800 AND 1820 GROUP BY Occupation ORDER BY total DESC, Occupation LIMIT 50

Unknown column 'Occupation' in 'order clause'"

This is the error - message.

Best regards

Bernhard

Share this post


Link to post
Share on other sites
Scott_Pryer

Well it works on my site and it is difficult to debug for you.  Here is another script with the last reference to 'occupation' removed. This also works on my site.

SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" and birthdate BETWEEN 1800 AND 1820 GROUP BY Occupation ORDER BY total DESC;

Can you confirm your MySQL version in Admin/Setup/Diagnostics? Mine are below.

MySQL 10.1.37-MariaDB (client)

MySQL 10.1.37-MariaDB-cll-lve (server)

Share this post


Link to post
Share on other sites
Appelmus

I think i have a MySQL Problem

client:Msql 5.0.12

server:Msql 5.5.62

I have to talk to the provider 

 

Share this post


Link to post
Share on other sites
Ken Roy
1 minute ago, Appelmus said:

I think i have a MySQL Problem

client:Msql 5.0.12

server:Msql 5.5.62

I think that is normal.  Mine are

MySQL mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $ (client)

tng_check.gif MySQL 5.7.23-percona-sure1-log (server)

Share this post


Link to post
Share on other sites
Appelmus

But scotts reports (sql queries) work for him, but not for me.

Share this post


Link to post
Share on other sites
Scott_Pryer

When you say it does not work, what do you mean?  Do you receive an error?

See attached view of the report results from my site.

Occupation report.jpg

Share this post


Link to post
Share on other sites
Appelmus

Thank you Scott and Ken

The problem appears to be in the database. I changed the time period, for example to 100 years. Then I got the only 2 occupations. 

berufe.jpg

During this period, however, there were many professions

Bildschirmfoto 2019-02-16 um 21.09.18.png

PS. How can I change the English term occupation to Beruf?

Share this post


Link to post
Share on other sites
Appelmus

I wrote the report now in the main website. There is only one tree there. And that's where it works

Big thanks to Scott

Now just two outstanding questions:
A) Can I disable the ability to download a csv file?
B) Where can I change the column headings?

Bildschirmfoto 2019-02-16 um 21.39.52.png

Ps The report is a fascinating tool. I can now formulate reports for different periods of time; For example, call from 1700 to 1800, from 1800 to 1825, from 1825 to 1850, etc. This shows how the social structure of a village has changed.

Share this post


Link to post
Share on other sites
Ken Roy
57 minutes ago, Appelmus said:

A) Can I disable the ability to download a csv file?

Install the CSV Reports for Admin only mod

59 minutes ago, Appelmus said:

B) Where can I change the column headings?

Go to TNG Admin > Custom Event Types and edit the custom event that you want to display in other languages, like OCCU (Occupation), expand the Other Languages and enter the text you want to use for that event in the languages you support on your site

Share this post


Link to post
Share on other sites
Appelmus

Thank you, Ken!

Share this post


Link to post
Share on other sites
Appelmus
vor 20 Stunden schrieb Scott_Pryer:

When you say it does not work, what do you mean?  Do you receive an error?

Hello Scott!

I believe the error occurred when I loaded the report with my ipad. With the desktop it works

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

×