This Help forum is for issues relates to webtrees 1.7. For issues related to webtrees 2.0, please use that forum.
Before asking for help please read "How to request help" by clicking on that tab above here."
  • Page:
  • 1

TOPIC: [SOLVED] Procedure for statistical numbers wanted

Procedure for statistical numbers wanted 2 weeks 3 days ago #1

  • Luenissla
  • Luenissla's Avatar Topic Author
  • Offline
  • Junior
  • Junior
  • Posts: 141
Hello tegether,

my installation Bergischer Datenpool has meanwhile grown to about 130 files.
So the determination of monthly static numbers becomes very tedious.
I note down the following statistics for each file:
Individuals
Total surnames
Families
Sources
media objects
Repositories
Total events
Total users
and the number of visitors on the first page.

I have collected the numbers in an Excel file.

Could someone here help me put together a procedure or does anyone already have one?
Best regards
Hans-Joachim (Lünenschloß)

Please Log in or Create an account to join the conversation.

Procedure for statistical numbers wanted 2 weeks 1 day ago #2

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 12929
Here is an SQL query that gives all counts for each tree:
SELECT
       gedcom_name,
       (SELECT COUNT(*) FROM wt_individuals WHERE gedcom_id=i_file) AS Individuals,
       (SELECT COUNT(*) FROM wt_families WHERE gedcom_id=f_file) AS Families,
       (SELECT COUNT(*) FROM wt_sources WHERE gedcom_id=s_file) AS Sources,
       (SELECT COUNT(*) FROM wt_media WHERE gedcom_id=m_file) AS Media,
       (SELECT COUNT(*) FROM wt_other WHERE gedcom_id=o_file AND o_type='REPO') AS Repositories,
       (SELECT COUNT(*) FROM wt_dates WHERE gedcom_id=d_file) AS Events,
       (SELECT COUNT(DISTINCT n_surn) FROM wt_name WHERE gedcom_id=n_file) AS Surnames,
       (SELECT page_count FROM wt_hit_counter WHERE wt_gedcom.gedcom_id=wt_hit_counter.gedcom_id AND page_name='index.php' AND page_parameter LIKE 'gedcom:%') AS Hits
FROM wt_gedcom
WHERE gedcom_id > 0
ORDER BY gedcom_name;

Here is the output from my development server:
+-------------+-------------+----------+---------+-------+--------------+--------+----------+------+
| gedcom_name | Individuals | Families | Sources | Media | Repositories | Events | Surnames | Hits |
+-------------+-------------+----------+---------+-------+--------------+--------+----------+------+
| demo        |          76 |       31 |       2 |    68 |            1 |    253 |       31 |   79 |
| genes       |        9119 |     2998 |     535 |  7043 |           24 |  27215 |     1715 |   23 |
| pg          |           1 |        0 |       0 |     0 |            0 |      3 |        1 |    2 |
| test        |      292760 |   113118 |     277 | 12257 |           15 | 859051 |    22871 |   24 |
+-------------+-------------+----------+---------+-------+--------------+--------+----------+------+

If you use phpMyAdmin, you can save the results as CSV - which you can read into Excel.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

Last edit: by fisharebest.

Procedure for statistical numbers wanted 2 weeks 1 day ago #3

  • Luenissla
  • Luenissla's Avatar Topic Author
  • Offline
  • Junior
  • Junior
  • Posts: 141
Hi, Greg,
thank you very much for your help.
It works very well and will help me a lot.
Best regards
Hans-Joachim (Lünenschloß)

Please Log in or Create an account to join the conversation.

[SOLVED] Procedure for statistical numbers wanted 2 weeks 1 day ago #4

  • Luenissla
  • Luenissla's Avatar Topic Author
  • Offline
  • Junior
  • Junior
  • Posts: 141
Problem solved.
Best regards
Hans-Joachim (Lünenschloß)

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
Powered by Kunena Forum