Skip site navigation (1) Skip section navigation (2)

Re: Question on collapsing a sparse matrix

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Bryan Emrys <bryan(dot)emrys(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Question on collapsing a sparse matrix
Date: 2009-04-27 20:15:53
Message-ID: 264855a00904271315w1372bcc7je47789a624a9d85d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, Apr 27, 2009 at 4:09 PM, Bryan Emrys <bryan(dot)emrys(at)gmail(dot)com> wrote:

> I've been handed a table that reminds me of a sparse matrix and I'm
> thinking that there should be some SQL way to simplify it.
> Assume table like (a column for every department, separate rows for each
> state if any department has headcount in the state, but each row has only
> one headcount entry):
>
> State    Dept1   Dept2   Dept3   Dept4
> AZ          3         NULL    NULL    NULL
> AZ        NULL       2        NULL    NULL
> AZ        NULL     NULL     17       NULL
> CA          2         NULL    NULL    NULL
> CA        NULL       21      NULL    NULL
> CA        NULL     NULL   NULL      6
> CA        NULL     NULL     4         NULL
> etc
>
> I'm trying to get to
>
> State    Dept1   Dept2   Dept3   Dept4
> AZ          3           2           17      NULL
> CA          2         21             4        6
> etc
>
> Is there some way of rolling up or ANDing records so that I can sum each
> state into a single record per state? This looks like something that would
> be obvious, but I'm apparently missing it. Any pointers would be
> appreciated.
>
> (BTW, there are a couple hundred departments in the actual table, they are
> not conveniently numbered and as you may guess from the example, there is
> not a consistent number of rows for each state; some have only 1 row, some
> have 40 or more, it simply depends on how many departments have headcount in
> that state.)
>

select State,sum(Dept1),sum(Dept2),sum(Dept3),sum(Dept4) from yourtable
group by State;

Sean

In response to

pgsql-novice by date

Next:From: David GardnerDate: 2009-04-27 20:19:41
Subject: Re: Question on collapsing a sparse matrix
Previous:From: Bryan EmrysDate: 2009-04-27 20:09:26
Subject: Question on collapsing a sparse matrix

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group