Sum qty for each hour

SQL++
1

Hi,

I have a query which gives me total qty scanned and timestamp. I would like to get total qty for each hour. I’m writing this in DBeaver which is my first time using this platform. Can anyone please help me how to sum qty hourly? I have attached the img for reference. This data goes for more than 3 months.

Thanks!

sum by hrs
sum by hrs793×183 5.26 KB

2

I don’t know DBeaver and can’t advise (you’d best ask in DBeaver forums) on how to use the tool to generate SQL if that’s what you’re after, but I see there is an SQL console for entering statements directly.

The SQL++ statement you would enter directly could resemble:

SELECT unit_sorter_type, sku_scanned_location_num, SUM(successful_sorted_sku_qty) hourly_qty, hour_stamp
FROM theCollection
LET hour_stamp = date_trunc_str(sku_scanned_event_tmstp, "hour")
WHERE ...
GROUP BY unit_sorter_type, sku_scanned_location_num, hour_stamp
ORDER BY ...

You of course have to group by all other elements you wish to select for the grouping to make sense.

Alternatively you could use:

WITH agg AS (
  SELECT SUM(successful_sorted_sku_qty) hourly_qty, hour_stamp
  FROM theCollection
  WHERE ...
  LET hour_stamp = date_trunc_str(sku_scanned_event_tmstp, "hour")
  GROUP BY hour_stamp
)
SELECT a.hourly_qty, a.hour_stamp, (SELECT DISTINCT a2.unit_sorter_type, a2.sku_scanned_location_num
                                    FROM theCollection a2
                                    WHERE date_trunc_str(a2.sku_scanned_event_tmstp, "hour") = a.hour_stamp
                                    AND ... 
                                   ) elements
FROM agg a
ORDER BY ...

or similar to return overall sums by hour and the distinct constituent other fields as a sub-array in the results.

Regardless of the query, the index advisor could be used to suggest indices to support the queries.

HTH.

1 Like