-2

I have a table contains data of year and value. I'd like to create a cross table with all the year as column headings and calculate descriptive statistics in the rows.

For instance, I'd like to calculate the mean and median value for year 2005, 2006, and 2007 separately and put them in the following table format. 

                    2005    2006    2007
Median   
Mean

To calculate the median and mean for year 2005, the code would be: 

SELECT avg(vallue),percentile_cont(0.5) WITHIN GROUP (ORDER BY value)  
FROM tablename WHERE year=2005;

but I don't know how to turn it into a table

S-Man
  • 22,521
  • 7
  • 40
  • 63
12B01
  • 171
  • 1
  • 1
  • 6

1 Answers1

0

in postgres "crosstab" is extension for pivot tables

for median I used this aggregate function and it was quite effective (fast and precise)

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Thanks! Maybe I didn't state my question clearly. I'm looking for a nested code which would allow me to calculate median and mean, and put them into a table format – 12B01 Jun 28 '16 at 15:27