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.

[Former Member]

Hi Tom,
which version of SQL Anywhere do you use? In v.17, the Pivot Table might be just what you need. If this is an option for you, and you need more hints, don't hesitate to ask me.

HTH
Volker Stöffler
DB-TecKnowledgy

[t1950]

I'm using SA 16