Combining rows in a select

[t1950]

i need to select 12 months of sales data for multiple items select item, ym_sales, 0, 0, 0, ... from ... where year - 2016 and month = 1 union select item, 0, ym_sales, 0, 0, ... from ... where year = 2016 and month = 2

more unions follow follow

i want the result set to contain 1 row per item with the 12 months of sales data.

i tried this based on an example in the SQL documentation

select item, sum( C1 ), sum( c2 ), sum( c3 ), .... from ( select item, ym_sales, 0, 0, 0, ... from ... where year - 2016 and month = 1 union select item, 0, ym_sales, 0, 0, ... from ... where year = 2016 and month = 2 ) group by item

which gave me an error

Thanks

[VolkerBarth]

from ( select item, ym_sales, 0, 0, 0, ... from ... where year - 2016 and month = 1 union select item, 0, ym_sales, 0, 0, ... from ... where year = 2016 and month = 2 ) group by ...

If you use a derived query (i.e. a SELECT block within parantheses in the FROM clause), you have to supply a name for it and name the columns, such as

from ( select item, ym_sales, 0 as ColX, 0 as ColY, 0 as ColZ, ... from ... where year - 2016 and month = 1 union select item, 0, ym_sales, 0, 0, ... from ... where year = 2016 and month = 2 ) DT group by ...

---

That being said, you might also be able to achive your aim by joining you data with a sa_rowgenerator(1, 12, 1) call to simply supply a dummy table with all month numbers...

[t1950]

adding the DT did the trick. Thanks for your help.