I am inserting rows to a new measurement from a subquery. The subquery returns 2 rows, but only one is actually inserted to the new measurement. In addition the time is set to 0, which means I had to set the duration in RETENTION POLICY "autogen" to before 1.1.1970.
This is the content of StoreSales:
INSERT StoreSales,StoreNumber="1",EnteredBy="Jake",Month=201906 value=1000
INSERT StoreSales,StoreNumber="1",EnteredBy="Jill",Month=201906 value=2000
INSERT StoreSales,StoreNumber="2",EnteredBy="Jill",Month=201905 value=2000
INSERT StoreSales,EnteredBy="Ann",Month=201906 value=1000
Set duration to before Unix epoch:
ALTER RETENTION POLICY "autogen" on "DT" duration 450000h0m0s
ALTER RETENTION POLICY "autogen" on "DT" shard duration 450000h0m0s
This is the insert I am trying to use: SELECT * INTO "StoreSalesByStoreByMonth" FROM ( SELECT Sum(value) FROM "StoreSales" WHERE StoreNumber !='' GROUP BY StoreNumber, Month)
The result is:
time written
---- -------
0 2
But StoreSalesByStoreByMonth only includes one record:
SELECT * FROM "StoreSalesByStoreByMonth" name: StoreSalesByStoreByMonth
time Month StoreNumber sum
---- ----- ----------- ---
0 201906 "1" 3000
The record for Month=201905, StoreNumber="2" is missing. There is on record in StoreSales without StoreNumber on purpose to verify that the group by excludes the records without that tag.
How can I get all the records from the subquery inserted? Can I set the time in the query somewhere, so I don't need to set the RETENTION POLICY "autogen" to before 1.1.1970?
