Appelmus Posted February 16, 2019 Report Share Posted February 16, 2019 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 Quote Link to comment Share on other sites More sharing options...
Scott_Pryer Posted February 16, 2019 Report Share Posted February 16, 2019 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; Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 16, 2019 Author Report Share Posted February 16, 2019 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 50Unknown column 'Occupation' in 'order clause'" This is the error - message. Best regards Bernhard Quote Link to comment Share on other sites More sharing options...
Scott_Pryer Posted February 16, 2019 Report Share Posted February 16, 2019 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) Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 16, 2019 Author Report Share Posted February 16, 2019 I think i have a MySQL Problem client:Msql 5.0.12 server:Msql 5.5.62 I have to talk to the provider Quote Link to comment Share on other sites More sharing options...
Ken Roy Posted February 16, 2019 Report Share Posted February 16, 2019 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) MySQL 5.7.23-percona-sure1-log (server) Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 16, 2019 Author Report Share Posted February 16, 2019 But scotts reports (sql queries) work for him, but not for me. Quote Link to comment Share on other sites More sharing options...
Scott_Pryer Posted February 16, 2019 Report Share Posted February 16, 2019 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. Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 16, 2019 Author Report Share Posted February 16, 2019 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. During this period, however, there were many professions PS. How can I change the English term occupation to Beruf? Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 16, 2019 Author Report Share Posted February 16, 2019 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? 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. Quote Link to comment Share on other sites More sharing options...
Ken Roy Posted February 16, 2019 Report Share Posted February 16, 2019 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 Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 16, 2019 Author Report Share Posted February 16, 2019 Thank you, Ken! Quote Link to comment Share on other sites More sharing options...
Appelmus Posted February 17, 2019 Author Report Share Posted February 17, 2019 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.