0

Hello I am using this query which gives me the results shown in the picture:

SELECT "t_stamp", "Total_Parts"
FROM myTable
WHERE "MachineName" = 'Main01'
AND "Quality" is not NULL
AND t_stamp >= '2023-08-10 16:30:00' AND t_stamp <= '2023-08-14 00:30:00'
ORDER BY 1 ASC

enter image description here

I wonder how can I get only the count for every hour... For example: I know I would have to sum the values from the Total_Parts column (in the red-box) but only for hour 17, and then the ones in the purple-box for the hour 18, and for the green-box for the hour 19.. Like the following table:

t_stamp Total_Parts
2023-08-10 17:00 2,628
2023-08-10 18:00 2,016
2023-08-10 19:00 1,913

I was looking at this link, but still could not figured it out. Any help would be very much appreciate it. Thank you.

1 Answer 1

0

This works for me:

SELECT date_trunc('hour', t_stamp) AS t_stamp,
       SUM(Total_Parts)            AS Total_Parts
FROM myTable
WHERE "MachineName" = 'Main01'
      AND "Quality" IS NOT NULL
      AND t_stamp >= '2023-08-10 16:30:00'
      AND t_stamp <= '2023-08-14 00:30:00'
GROUP BY date_trunc('hour', t_stamp)
ORDER BY 1 ASC;

Oh, and, next time please add scripts for objects and data please :)

2
  • Hi Thank you very much for your response. I honestly do not kow how to "add scripts for objects and data"... Can you please share how can start learning that. Thank you very much. Your solution worked for me.
    – Lalo Quera
    Commented Aug 18, 2023 at 15:18
  • You could create a script for the CREATE TABLE and INSERT data. For example through sqlfiddle.com
    – Peter
    Commented Aug 22, 2023 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.