R6 beta - dblp

SELECT aij1.Journal, COUNT( art1.PubID ) AS art_count, COUNT(art1.PubID)/(2012-MIN_YEAR.minYear) AS art_avg
FROM article_in_journal aij1, pub_article art1, (
    SELECT pub.YEAR AS 'minYear', aij2.Journal AS 'minYearJour'
        FROM publication pub, article_in_journal aij2
        WHERE pub.PubKey=aij2.ArtID
        GROUP BY aij2.Journal
        HAVING MIN(pub.YEAR)
) MIN_YEAR -- get minimum year of publication for each journal
WHERE art1.PubID = aij1.ArtID AND MIN_YEAR.minYearJour=aij1.Journal
GROUP BY aij1.Journal
HAVING MAX( art1.Volume ) > (-- keep only journal above average
    SELECT AVG( volume )
    FROM (
        SELECT art.Volume AS 'volume'
        FROM pub_article art, article_in_journal aij
        WHERE art.PubID = aij.ArtID
        GROUP BY aij.Journal
        HAVING MAX( art.Volume )
    ) VOLUME_PER_JOURNAL -- get average volume per year for each journal
)
-- Showing rows 0 - 29 ( 87 total, Query took 78.1749 sec)