Jump to content
TNG Community

reports II


Appelmus

Recommended Posts

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

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...