How to Aggregate on DATE_DIFF_STR values

SQL++
1

I am wondering what the best way would be to create the following

I need to get the Sum of All Sales Prices from “Record.Prices.ClosePrice AS Sold_Price”
I need the avg of the Sales Prices from “Record.Prices.ClosePrice AS Sold_Price”
And I need the Count of all records in that query

I need the min value of " DATE_DIFF_STR(Record.Dates.PurchaseContractDate, Record.Dates.OnMarketDate, ‘day’) as Days_on_Market"
I need the average of " DATE_DIFF_STR(Record.Dates.PurchaseContractDate, Record.Dates.OnMarketDate, ‘day’) as Days_on_Market"

I tried the below query with not much success, when i go and run it the count works, the sum and avg for close price returns null and the date-diff min and avg dont even run

SELECT COUNT(*) AS Closed_Sales,
       AVG(Record.Prices.ClosePrice) AS Average_Sold_Price,
       SUM(Record.Prices.ClosePrice) AS Total_Volume_Sold,
       MIN(DATE_DIFF_STR(Record.Dates.PurchaseContractDate, Record.Dates.OnMarketDate, 'day') AS Shortest_Time_on_Market, AVG(DATE_DIFF_STR(Record.Dates.PurchaseContractDate, Record.Dates.OnMarketDate, 'day') AS AVERAGE_Time_on_Market
               FROM rets
               WHERE _type = 'Residential'
                   AND ListingId IN ["OC22059523", "OC22069390", "NDP2203000", "OC22036622", "OC22009860" ]
2

The following should work.
If not working post the sample or the filed values of the document that refrenced.
SUM/AVG requires number. If giving null means Record.Prices.ClosePrice thing wrong in the path (arrays/object/case sensitive of field or typo)

Remove WITH clause and try

WITH rets AS ([{"Record":{"Prices":{"ClosePrice":1000}, "Dates":{"PurchaseContractDate":"2022-06-07", "OnMarketDate":"2022-06-01"}},"_type":"Residential", "ListingId":"OC22059523"},
               {"Record":{"Prices":{"ClosePrice":2000}, "Dates":{"PurchaseContractDate":"2022-06-17", "OnMarketDate":"2022-05-01"}},"_type":"Residential", "ListingId":"NDP2203000"}])
SELECT COUNT(1) AS Closed_Sales,
       AVG(r.Record.Prices.ClosePrice) AS Average_Sold_Price,
       SUM(r.Record.Prices.ClosePrice) AS Total_Volume_Sold,
       MIN(DATE_DIFF_STR(r.Record.Dates.PurchaseContractDate, r.Record.Dates.OnMarketDate, 'day')) AS Shortest_Time_on_Market,
       AVG(DATE_DIFF_STR(r.Record.Dates.PurchaseContractDate, r.Record.Dates.OnMarketDate, 'day')) AS AVERAGE_Time_on_Market
FROM rets AS r
WHERE r._type = 'Residential' AND r.ListingId IN ["OC22059523", "OC22069390", "NDP2203000", "OC22036622", "OC22009860" ];
3

Ok i get now some more data with the output be like this

[
{
“AVERAGE_Time_on_Market”: 6.2,
“Average_Sold_Price”: null,
“Closed_Sales”: 5,
“Shortest_Time_on_Market”: -30,
“Total_Volume_Sold”: null
}
]

which means the it does not pickup any Price data

4

If you post sample document will able to tell what is wrong

5

I think i figured out the issue, its due to fact that the values are in a string instead of a number so i have to go and convert them.

Changing to

AVG(TONUMBER(r.Record.Prices.ClosePrice)) AS Average_Sold_Price,
SUM(TONUMBER(r.Record.Prices.ClosePrice)) AS Total_Volume_Sold,

creates now what i am looking for

[
  {
    "AVERAGE_Time_on_Market": 6.2,
    "Average_Sold_Price": 1524000,
    "Closed_Sales": 5,
    "Shortest_Time_on_Market": -30,
    "Total_Volume_Sold": 7620000
  }
]

It would be nice if the query would throw an error if one try’s to do mathematical operations on a string

1 Like