on 2016 Dec 06 10:52 AM
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
Request clarification before answering.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
9 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.