DROP TABLE meanphonemevalues; 

SELECT 
phonemesphoneme.speaker, 
phonemesphoneme.style,
phonemesphoneme.value, 
COUNT(phonemesphoneme.id) AS number,

AVG(phonemesphoneme.duration) AS meanduration,
(
   SUM(phonemesphoneme.duration*phonemesphoneme.duration) - 
   SUM(phonemesphoneme.duration) * SUM(phonemesphoneme.duration) / COUNT(phonemesphoneme.id)
) AS sdduration,

AVG(cog_fm.time2) AS meancog,
(
   SUM(cog_fm.time2*cog_fm.time2) - 
   SUM(cog_fm.time2) * SUM(cog_fm.time2) / COUNT(cog_fm.id)
)  AS sdcog,

AVG(intensity_hm.time2) AS meanintensity,
(
   SUM(intensity_hm.time2*intensity_hm.time2) - 
   SUM(intensity_hm.time2) * SUM(intensity_hm.time2) / COUNT(intensity_hm.id)
) as sdintensity,

AVG(formantsemitones_fm.f12dist) as meanf12dist,
(
   SUM(formantsemitones_fm.f12dist*formantsemitones_fm.f12dist) - 
   SUM(formantsemitones_fm.f12dist) * SUM(formantsemitones_fm.f12dist) / COUNT(formantsemitones_fm.id)
) as sdf12dist

INTO TABLE meanphonemevalues 
FROM phonemesphoneme, cog_fm, intensity_hm, formantsemitones_fm
WHERE 
phonemesphoneme.durationvalid = 'V' AND
cog_fm.id = phonemesphoneme.id AND
phonemesphoneme.id = cog_fm.id AND
intensity_hm.id = phonemesphoneme.id AND
phonemesphoneme.id = intensity_hm.id AND
formantsemitones_fm.id = phonemesphoneme.id AND
phonemesphoneme.id = formantsemitones_fm.id
GROUP BY
phonemesphoneme.speaker, 
phonemesphoneme.style,
phonemesphoneme.value;
      
GRANT SELECT ON meanphonemevalues TO PUBLIC;
CREATE INDEX meanphonemevalues_index on meanphonemevalues (speaker, style, value);

UPDATE meanphonemevalues SET 
sdduration  = SQRT(sdduration / (number - 1)),
sdcog  = SQRT(sdcog / (number - 1)),
sdintensity  = SQRT(sdintensity / (number - 1)),
sdf12dist  = SQRT(sdf12dist / (number - 1))
WHERE number > 1;

UPDATE meanphonemevalues SET 
sdduration  = NULL,
sdcog  = NULL,
sdintensity = NULL,
sdf12dist  = NULL
WHERE number <= 1;

