How to create columns based on the value of one other column?

Question

I have a table with accidents (~110,000 records) and a table with municipalities (~400 records). All accidents have a municipality code of the municipality where they occurred and I can count the number of accidents per municipality. For my project I need numbers / municipality.

One of the columns of the accidents table contains the type of object against which the accident occurred, e.g. car, truck, bicycle, etc. What I would like is to create new columns for each object type (car, truck, etc) with a count for each municipality.

Edit

Example of accidents table

mun_code objecttype
1        car
1        truck
1        car
2        bicycle
2        car
2        bicycle
2        truck
3        tree

Desired output

mun_code car truck bicycle tree
1        2   1     null    null
2        1   1     2       null
3        null null null    1

Instead of null, 0 is ok.

Edit 2

The point is that I have many columns that I have to aggregate over municipalities. I just wonder whether it is possible to have a script that automatically creates the needed columns for each objecttype and automatically aggregates for each objecttype

Is this possible to do in (postgres) SQL and how should I do this?

Please add sample data and expected output! – S-Man Aug 22 '19 at 13:28 — S-Man, Aug 22 '19 at 13:28
Sorry, you're right. Added that. – Arnold Aug 22 '19 at 13:47 — Arnold, Aug 22 '19 at 13:47

S-Man · Answer 1 · 2019-08-22T14:22:24.330

2

demo:db<>fiddle

Creating a pivot table can be achieved by using GROUP BY and FILTER clause

SELECT
    mun_code,
    COUNT(*) FILTER (WHERE object = 'car') as car,
    COUNT(*) FILTER (WHERE object = 'truck') as truck,
    COUNT(*) FILTER (WHERE object = 'bike') as bike,
    COUNT(*) FILTER (WHERE object = 'tree') as tree
FROM
    mytable
GROUP BY mun_code

If you really need NULL values instead of 0, you can use the NULLIF() function afterwards

demo:db<>fiddle

NULLIF(COUNT(*) FILTER (WHERE object = 'car'), 0)

Edit: For dynamic numbers of columns, which is not simply achieveable, there is a small workaround using JSON:

demo:db<>fiddle

SELECT
    mun_code,
    jsonb_object_agg(object, count) AS jsonobject
FROM (
    SELECT
        mun_code,
        object,
        COUNT(*)
    FROM
        mytable t
    GROUP BY mun_code, object
) s
GROUP BY mun_code

With this, instead doing

SELECT car FROM my_pivoted_result

you can do:

SELECT jsonobject ->> 'car' FROM my_pivoted_result

edited Aug 22 '19 at 14:22

answered Aug 22 '19 at 13:52

S-Man

22,521
7
40
63

same as above, I have many such columns and don't know all types, so how do I generatre the columns for each type? – Arnold Aug 22 '19 at 13:59
Well that's not really simple. A query always needs to return the same number of columns. – S-Man Aug 22 '19 at 14:01
Could you elaborate on that? I don't see how the columns and aggregates are generated from the contents of the objecttype column. – Arnold Aug 22 '19 at 14:03
Further reading on "dynamic pivot" https://www.postgresql.org/message-id/CAH3i69kyvE09SH42Vh%2B-UxTmWCJ1tiSC%3DeLg55m7U6WyLrHo3g%40mail.gmail.com – S-Man Aug 22 '19 at 14:15
Great answer from @ErwinBrandstetter: https://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by – S-Man Aug 22 '19 at 14:20
added a little work around for dynamic columns using the a json object – S-Man Aug 22 '19 at 14:22
+1 for your elaboration, thanks. But this is still not what I need: - I get rows instead of columns (this is good SQL practice I noticed researching your links, thank you very much) and my post processing app (QGis) requires columns as far as I can see. And rows without a specific objecttype do not occur in the results. Nice to point me to the jsonobject, this is a really interesting function. – Arnold Aug 22 '19 at 19:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/198350/discussion-between-s-man-and-arnold). – S-Man Aug 23 '19 at 06:53

score 0 · Answer 2 · answered Aug 22 '19 at 13:29

0

you can use conditional aggregaiton

    select sum(case when type='car' then 1 else 0 end) as car,
    sum(case when type='truck' then 1 else 0 end) as truck,
    sum(case when type='bicycle' then 1 else 0 end) as bicycle

answered Aug 22 '19 at 13:29

Zaynul Abadin Tuhin

31,407
5
33
63

I have many such columns and don't know all types, so how do I generatre the columns for each type? – Arnold Aug 22 '19 at 13:59

How to create columns based on the value of one other column?

2 Answers2