Linear Frequency
MAT 259, 2013
Giovanni
Introduction
Question:
I would like to visualize the “presence” of the oldest 15 European Union member countries in the Library in 2011 (and compare it with the PIL (GDP) , population, squaremeters...)
Some statistics:
EU15 (1995)
English: EU 27: Further European Union enlargement
Current members
Candidate and acceding (Croatia) countries
Potential candidate countries
Membership possible
http://it.wikipedia.org/wiki/Stati_membri_dell%27Unione_europea (ita)
http://en.wikipedia.org/wiki/Member_state_of_the_European_Union (eng)
Currently the EU is composed by 27 members, but because of the small numbers of the last joining countries, I used the first 15 members (chronologically) the correspond at the EU situation in 1995.
Background
and Sketches
Query
SELECT
year(cout) as 'Year',
SUM(CASE WHEN title like "% belgium %" THEN 1 ELSE 0 END) as Belgium,
SUM(CASE WHEN title like "% france %" THEN 1 ELSE 0 END) as France,
SUM(CASE WHEN title like "% germany %" THEN 1 ELSE 0 END) as Germany,
SUM(CASE WHEN title like "% italy %" THEN 1 ELSE 0 END) as Italy,
SUM(CASE WHEN title like "% netherlands %" or title like "% holland %" THEN 1 ELSE 0 END) as Netherlands,
SUM(CASE WHEN title like "% denmark %" THEN 1 ELSE 0 END) as Denmark,
SUM(CASE WHEN title like "% ireland %" THEN 1 ELSE 0 END) as Ireland,
SUM(CASE WHEN title like "% united kingdom %" or title like "% england %"
or title like "% scotland %" or title like "% wales %" or title like "% nothern ireland %" THEN 1 ELSE 0 END) as UK,
SUM(CASE WHEN title like "% greece %" THEN 1 ELSE 0 END) as Greece,
SUM(CASE WHEN title like "% portugal %" THEN 1 ELSE 0 END) as Portugal,
SUM(CASE WHEN title like "% spain %" THEN 1 ELSE 0 END) as Spain,
SUM(CASE WHEN title like "% austria %" THEN 1 ELSE 0 END) as Austria,
SUM(CASE WHEN title like "% finland %" THEN 1 ELSE 0 END) as Finland,
SUM(CASE WHEN title like "% sweden %" THEN 1 ELSE 0 END) as Sweden
FROM
inraw
where
year(cout) = '2011'
GROUP BY
year(cout);
Query results
Results and Analysis
Code