« IE emulator on Ubuntu | Main | Ubuntu's Computer Janitor »
March 21, 2011
Pulling author and publication per year counts
Just a bit of sql documentation for now:
Pull all authors along with NID of their publications:
SELECT drupal_biblio.nid, drupal_biblio_contributor_data.cid, drupal_biblio_contributor_data.name FROM drupal_biblio LEFT JOIN drupal_biblio_contributor ON drupal_biblio_contributor.nid = drupal_biblio.nid LEFT JOIN drupal_biblio_contributor_data ON drupal_biblio_contributor_data.cid = drupal_biblio_contributor.cid
SELECT drupal_biblio_contributor_data.name, count(drupal_biblio_contributor_data.name) FROM drupal_biblio LEFT JOIN drupal_biblio_contributor ON drupal_biblio_contributor.nid = drupal_biblio.nid LEFT JOIN drupal_biblio_contributor_data ON drupal_biblio_contributor_data.cid = drupal_biblio_contributor.cid GROUP BY drupal_biblio_contributor_data.name ORDER BY count(drupal_biblio_contributor_data.name) DESC
Pull Pubs per Year:
SELECT drupal_biblio.biblio_year, count(drupal_biblio.biblio_year)
FROM drupal_biblio
GROUP BY drupal_biblio.biblio_year
ORDER BY count(drupal_biblio.biblio_year) DESC
Pull Keywords used by Publications per year (remove group by year for variant):
SELECT drupal_biblio.biblio_year, drupal_term_data.name, COUNT(drupal_term_data.name)
FROM drupal_biblio
LEFT JOIN drupal_term_node ON drupal_term_node.nid = drupal_biblio.nid
LEFT JOIN drupal_term_data ON drupal_term_data.tid = drupal_term_node.tid
GROUP BY drupal_biblio.biblio_year, drupal_term_data.name
ORDER BY COUNT(drupal_term_data.name) DESC
- Bonus: Put results of this query (OK, modify a bit first) into a tag cloud. This website recognizes phrases.
Posted by kkwaiser at March 21, 2011 05:13 PM