Geek Answers Handbook

PostgreSQL cross tab with three columns with values ​​summed from one column

I am new to SQL and trying to do a crosstab in Postgres. I would do it in Excel, but I have a database of about 3.5 million rows, 20,000 different values ​​for the code, 7 categories in cat and variable values ​​from 1 to 100. The code can only have a few of 7 categories.

Excel cannot handle the number of rows, so SQL is.

My details are in the form

code   |  cat        |   value |
--------------------------------
abc123 |   1         |    4    |
abc234 |   2         |    6    |
abc345 |   1         |    1    |
abc123 |   3         |    2    |
abc123 |   6         |    12   |

with code and cat as text, the value as an integer stored in a Postgres table.

I would like to do a crosstab for code and cat with the sum of the value. I would like it to display null instead of "null" in the return, but if "null" would be a simpler query, then that would be fine.

So I would like to get

code   |   'cat=0' | 'cat=1' | 'cat=2' | 'cat=3' | 'cat=4' | 'cat=5' | 'cat=6'|
abc123 |    25     |  0      |  3      |  500    | 250     | 42      |  0     |
abc234 |     0     |  100    |  0      |   10    |  5      |  0      |   25   |
abc345 |    1000   |   0     |  0      |    0    |  0      |  0      |   0    |

Postgres ; SO PostgreSQL Crosstab Query, , .

.

+5
2

, :

select * from crosstab(
'select code, cat, sum(value) as value
 from my_table 
 group by code, cat
 order by 1,2'
) as ct(code varchar(255),
    cat_0 bigint,
    cat_1 bigint, 
    cat_2 bigint, 
    cat_3 bigint, 
    cat_4 bigint, 
    cat_5 bigint, 
    cat_6 bigint)

, select - ct , -.

+5

Try:

select * from crosstab(
'select code, cat, sum(value) as value
 from my_table 
 group by code, cat
 order by 1,2'
) as ct(code text,
        cat_0 int,
        cat_1 int, 
        cat_2 int, 
        cat_3 int, 
        cat_4 int, 
        cat_5 int, 
        cat_6 int)
+3

More articles:


All Articles