0

In my line of work, rollup refers to combining/averaging months or quarters. In this case, the rollup is based on INDCODE and PERIOD. I have a table named INDUSTRY that contains data from quarters 1, 2, 3, and 4. Below is a sample of what it looks like.

State    area    periodyear   period   indcode    firms    avgemp
  32    000001       2016       01      447125      25       412
  32    000001       2016       02      447125      28       427
  32    000001       2016       03      447125      29       429
  32    000001       2016       04      447125      26       385

First, I am trying to average certain fields but not all. In this case, it would be firms and avgemp. If I was wanting it to look like the desired result below, can I use the rollup function? I want the new averaged data to be in the same table as quarters 1, 2, 3, and 4. Is it better to put the averaged data in a new table?

State    area    periodyear   period   indcode    firms    avgemp
  32    000001       2016       01      447125      25       412
  32    000001       2016       02      447125      28       427
  32    000001       2016       03      447125      29       429
  32    000001       2016       04      447125      26       385
  32    000001       2016       00      447125      27       413
Tim Wilcox
  • 1,275
  • 2
  • 19
  • 43
  • 1
    "it depends" is so often the answer I'm afraid. If everyone understands that period `00` refers to a whole year and your existing reports query the table appropriately then using 1 table is fine. However if there are adverse effect on existing queries/reports then perhaps another table of yearly data would make sense. – Paul Maxwell Oct 04 '17 at 00:33
  • I would be inclined to use `NULL` rather than `00` for the period. – Gordon Linoff Oct 04 '17 at 00:43
  • @Used_By_Already. A period of 00 is understood by all. There are 22 fields in the actual table and 9 primary keys; one of which is period. – Tim Wilcox Oct 04 '17 at 15:51
  • Next, not to sound naive but how does one create the new calculated data? I know about Select but that just shows it to you. – Tim Wilcox Oct 04 '17 at 15:59

0 Answers0