Re: Calculation of per Capita on-the-fly - problems with SQL syntax

On Wed, Oct 17, 2007 at 08:29:58AM +0200, Stefan Schwarzer wrote:
> Hmmm.... Actually, my intention was to get a more "excel" like output, that
> is the formatting into year-columns. This eases a lot the PHP/HTML
> display/loop. Otherwise I would have to start to do some joggling inside
> PHP to get it that way....

When you say "excel-like output" do you mean some sort of cross-
tabulated output? i.e. instead of

n y v
x 01 5
x 02 7
y 01 3
z 02 4

you want:

n 01 02
x 5 7
y 3
z 4

or something like that? If you do then I tend to get that effect by
doing something like:

SELECT n,
SUM(CASE WHEN y = 01 THEN v END) AS y01,
SUM(CASE WHEN y = 02 THEN v END) AS y02
FROM table
GROUP BY n
ORDER BY n;

This gets a bit fiddly if you're doing lots of columns/categories, but
gives lots of flexibility with the columns you produce. There's a
subdirectory in the contrib directory called "tablefunc" that provides
some code to do crosstabs if all you want to do is something simple.

If you're doing it by hand, you have the advantage of being able to have
something like:

SUM(v) AS total

at the end, or maybe a mean (AVG) or standard deviation (STDDEV) if it
makes sense to do anything like that.

Sam