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

howing rows 0 - 0 ( 1 total, Query took 51.9760 sec)
AVG(volume)
83.69727891156462

###############

SELECT aij1.Journal
FROM article_in_journal aij1, pub_article art1
WHERE art1.PubID=aij1.ArtID
GROUP BY aij1.Journal
HAVING MAX(art1.Volume) > (
	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
)
Showing rows 0 - 29 ( 87 total, Query took 45.1560 sec)
 Journal
	Edit Edit 	Copy Copy 	Delete Delete 	ADS
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. Artificial Intellegence
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. Bioinformatics
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. Fuzzy Systems
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. Human-Computer Interaction
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. in MM
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. Operations Research
	Edit Edit 	Copy Copy 	Delete Delete 	Adv. Software Engineering
	Edit Edit 	Copy Copy 	Delete Delete 	Ann. Pure Appl. Logic
	Edit Edit 	Copy Copy 	Delete Delete 	Annals OR
	Edit Edit 	Copy Copy 	Delete Delete 	Applied Comp. Int. Soft Computing
	Edit Edit 	Copy Copy 	Delete Delete 	Applied Mathematics and Computation
	Edit Edit 	Copy Copy 	Delete Delete 	Archive of Formal Proofs
	Edit Edit 	Copy Copy 	Delete Delete 	Ars Comb.
	Edit Edit 	Copy Copy 	Delete Delete 	Artif. Intell.
	Edit Edit 	Copy Copy 	Delete Delete 	BIAA
	Edit Edit 	Copy Copy 	Delete Delete 	Biological Cybernetics




SELECT aij1.Journal, COUNT( art1.PubID ) AS art_count
FROM article_in_journal aij1, pub_article art1
WHERE art1.PubID = aij1.ArtID
GROUP BY aij1.Journal
HAVING MAX( art1.Volume ) > (
	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
)
--Showing rows 0 - 29 ( 87 total, Query took 46.6414 sec)



SELECT aij1.Journal, COUNT( art1.PubID ) AS art_count
FROM article_in_journal aij1, pub_article art1
WHERE art1.PubID = aij1.ArtID
GROUP BY aij1.Journal
HAVING MAX( art1.Volume ) > (
	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
)
-- Pour avoir l'année de première parution d'un article, on pourrait
-- faire un (SELECT MIN(pub.YEAR) AS 'year' FROM publication pub, pub_article_in_journal art
-- WHERE pub.PubKey=art.ArtID AND art.Journal='journal') MIN_YEAR
-- où 'journal' est l'un des journaux sélectionnés.
-- On divise ensuite par [ 2011-MIN_YEAR.year]


SELECT aij1.Journal, COUNT( art1.PubID ) AS art_count, (2012-MIN_YEAR.minYear)
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
WHERE art1.PubID = aij1.ArtID AND MIN_YEAR.minYearJour=aij1.Journal
GROUP BY aij1.Journal
HAVING MAX( art1.Volume ) > (
	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
)
-- Showing rows 0 - 29 ( 87 total, Query took 79.9208 sec)



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 min( pub.Year ) AS 'minYear', aij2.Journal AS 'minYearJour'
FROM publication pub, article_in_journal aij2
WHERE pub.PubKey = aij2.ArtID
GROUP BY aij2.Journal
)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 74.8864 sec)



SELECT aij1.Journal, COUNT( art1.PubID ) AS art_count, COUNT(art1.PubID)/(2012-MIN_YEAR.minYear) AS art_avg, COUNT(art1.PubID)/TOTAL_AUTHOR.totalAut AS author_average
FROM article_in_journal aij1, pub_article art1, (
	SELECT MIN(pub.Year) AS 'minYear', aij2.Journal AS 'minYearJour'
        FROM publication pub, article_in_journal aij2
        WHERE pub.PubKey=aij2.ArtID
        GROUP BY aij2.Journal
) MIN_YEAR, -- get minimum year of publication for each journal
(	SELECT COUNT(pa.AuthorName) AS totalAut
	FROM pub_author pa, pub_article art2
	WHERE pa.PubID=art2.PubID
	GROUP BY pa.PubID
) TOTAL_AUTHOR

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
)
-- TROP LONG




SELECT JOURNAL_ABOVE_AVG.journalSupAvg, COUNT( art3.PubID ) AS art_count
FROM article_in_journal aij3, pub_article art3,(
	SELECT MIN(pub.Year) AS 'minYear', aij2.Journal AS 'minYearJour'
        FROM publication pub, article_in_journal aij2
        WHERE pub.PubKey=aij2.ArtID
        GROUP BY aij2.Journal
) MIN_YEAR, -- get minimum year of publication for each journal
(
	SELECT aij1.Journal AS journalSupAvg
	FROM article_in_journal aij1, pub_article art1
	WHERE art1.PubID = aij1.ArtID
	GROUP BY aij1.Journal
	HAVING MAX( art1.Volume ) > (-- keep only journals 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
	)
) JOURNAL_ABOVE_AVG
WHERE art3.PubID=aij3.ArtID AND aij3.Journal=JOURNAL_ABOVE_AVG.journalSupAvg
-- 400 secondes + problèmes de DISTINCT



--------------------Temp, rien pour le moment
SELECT aij1.Journal, COUNT( art1.PubID ) AS art_count, COUNT(art1.PubID)/(2012-MIN_YEAR.minYear) AS art_avg, COUNT(art1.PubID)/TOTAL_AUTHOR.totalAut AS author_average
FROM article_in_journal aij1, pub_article art1, (
	SELECT MIN(pub2.Year) AS 'minYear', aij2.Journal AS 'minYearJour'
        FROM publication pub2, article_in_journal aij2
        WHERE pub2.PubKey=aij2.ArtID
        GROUP BY aij2.Journal
) MIN_YEAR, -- get minimum year of publication for each journal
(	SELECT COUNT(pa3.AuthorName) AS totalAut
	FROM pub_author pa3, pub_article art3, article_in_journal aij3
	WHERE pa3.PubID=art3.PubID AND aij3.ArtID=art3.PubID AND aij3.Journal=aij1.Journal
	GROUP BY pa.PubID
) TOTAL_AUTHOR

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
)
-------------------------------


SELECT SELECTION_JOURNAL.selJourn
FROM
	SELECT aij1.Journal as selJourn
	FROM article_in_journal aij1, pub_article art1
	WHERE art1.PubID=aij1.ArtID
	GROUP BY aij1.Journal
	HAVING MAX(art1.Volume) > (
		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
	) SELECTION_JOURNAL
--Showing rows 0 - 29 ( 87 total, Query took 49.0934 sec)



--Affiche journaux et total articles
SELECT SELECTION_JOURNAL.selJourn, SELECTION_JOURNAL.totArt
FROM (
	SELECT aij1.Journal as selJourn, COUNT(aij1.ArtID) as totArt
	FROM article_in_journal aij1, pub_article art1
	WHERE art1.PubID=aij1.ArtID
	GROUP BY aij1.Journal
	HAVING MAX(art1.Volume) > (
		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
	) 
)SELECTION_JOURNAL
--Showing rows 0 - 29 ( 87 total, Query took 54.4684 sec)




SELECT SELECTION_JOURNAL.selJourn, SELECTION_JOURNAL.totArt, COUNT(pa2.AuthorName)
FROM (
	SELECT aij1.Journal as selJourn, COUNT(aij1.ArtID) as totArt
	FROM article_in_journal aij1, pub_article art1
	WHERE art1.PubID=aij1.ArtID
	GROUP BY aij1.Journal
	HAVING MAX(art1.Volume) > (
		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
	) 
)SELECTION_JOURNAL, pub_author pa2, pub_article art2, article_in_journal aij2
WHERE pa2.PubID=art2.PubID AND aij2.ArtID=art2.PubID AND aij2.Journal=VOLUME_PER_JOURNAL.selJourn
GROUP BY SELECTION_JOURNAL.selJourn
