cancel
Showing results for 
Search instead for 
Did you mean: 

Combining rows in a select

t1950
Participant
0 Kudos
2,510

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

Accepted Solutions (0)

Answers (2)

Answers (2)

VolkerBarth
Contributor

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
Participant
0 Kudos

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
Participant
0 Kudos

I'm using SA 16