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

asked 06 Dec '16, 10:52

Tom%20Mangano's gravatar image

Tom Mangano
672242738
accept rate: 8%


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...

permanent link

answered 06 Dec '16, 10:58

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

edited 06 Dec '16, 11:01

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

(06 Dec '16, 12:20) Tom Mangano

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

permanent link

answered 06 Dec '16, 11:34

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
5453715
accept rate: 25%

edited 06 Dec '16, 11:34

I'm using SA 16

(06 Dec '16, 11:52) Tom Mangano
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×68

question asked: 06 Dec '16, 10:52

question was seen: 2,029 times

last updated: 06 Dec '16, 12:20