« 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

Comments

Login to leave a comment. Create a new account.