array_agg group by and null

Question

Given this table:

SELECT * FROM CommodityPricing order by dateField

"SILVER";60.45;"2002-01-01"
"GOLD";130.45;"2002-01-01"
"COPPER";96.45;"2002-01-01"
"SILVER";70.45;"2003-01-01"
"GOLD";140.45;"2003-01-01"
"COPPER";99.45;"2003-01-01"
"GOLD";150.45;"2004-01-01"
"MERCURY";60;"2004-01-01"
"SILVER";80.45;"2004-01-01"

As of 2004, COPPER was dropped and mercury introduced.
How can I get the value of (array_agg(value order by date desc) ) [1] as NULL for COPPER?

select commodity,(array_agg(value order by date desc) ) --[1]
from CommodityPricing
group by commodity

"COPPER";"{99.45,96.45}"
"GOLD";"{150.45,140.45,130.45}"
"MERCURY";"{60}"
"SILVER";"{80.45,70.45,60.45}"

SQLFiddle for this sample data: http://sqlfiddle.com/#!15/f27f4 — Craig Ringer, Mar 17 '14 at 01:49
[So do you have your answer?](http://meta.stackexchange.com/a/5235/169168) — Erwin Brandstetter, Jul 24 '15 at 03:25

score 0 · Answer 1 · answered Mar 17 '14 at 10:26

0

SQL Fiddle

select
    commodity,
    array_agg(
        case when commodity = 'COPPER' then null else price end
        order by date desc
    )
from CommodityPricing
group by commodity
;

answered Mar 17 '14 at 10:26

Clodoaldo Neto

108,856
25
211
247

score 0 · Answer 2 · answered Mar 18 '14 at 13:28

To "pad" missing rows with NULL values in the resulting array, build your query on full grid of rows and LEFT JOIN actual values to the grid.
Given this table definition:

CREATE TEMP TABLE price (
    commodity text
  , value     numeric
  , ts        timestamp  -- using ts instead of the inappropriate name date 
);

I use generate_series() to get a list of timestamps representing the years and CROSS JOIN to a unique list of all commodities (SELECT DISTINCT ...).

SELECT commodity, (array_agg(value ORDER BY ts DESC)) AS years
FROM   generate_series ('2002-01-01 00:00:00'::timestamp
                      , '2004-01-01 00:00:00'::timestamp
                      , '1y') t(ts)
CROSS  JOIN (SELECT DISTINCT commodity FROM price) c(commodity)
LEFT   JOIN price p USING (ts, commodity)
GROUP  BY commodity;

Result:

COPPER  {NULL,99.45,96.45}
GOLD    {150.45,140.45,130.45}
MERCURY {60,NULL,NULL}
SILVER  {80.45,70.45,60.45}

SQL Fiddle.
_{I cast the array to text in the fiddle, because the display sucks and would swallow NULL values otherwise.}

array_agg group by and null

2 Answers2

Linked