Обсуждение: Question on collapsing a sparse matrix
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.)
Bryan
On Mon, Apr 27, 2009 at 4:09 PM, Bryan Emrys <bryan.emrys@gmail.com> wrote:
select State,sum(Dept1),sum(Dept2),sum(Dept3),sum(Dept4) from yourtable group by State;
Sean
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 Dept4AZ 3 NULL NULL NULLAZ NULL 2 NULL NULLAZ NULL NULL 17 NULLCA 2 NULL NULL NULLCA NULL 21 NULL NULLCA NULL NULL NULL 6CA NULL NULL 4 NULLetcI'm trying to get toState Dept1 Dept2 Dept3 Dept4AZ 3 2 17 NULLCA 2 21 4 6etcIs 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
Take a look into the crosstab function in the tablefunc contrib module. http://www.postgresql.org/docs/8.3/static/tablefunc.html Might be able to do what you are describing. Bryan Emrys 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.) > > Bryan > -- David Gardner