PostgreSQL should return both count and rows within a single statement

Question

I have a table that stores entries along with their timestamp. My node.js API should return both the count of rows and count of entries grouped by hour.

example:

{
    "count":"6",
    "countByHour":[
        "00":"1",
        "08":"2",
        "16":"3"
    ]
}

What should be the SQL query to generate this result?

clemens · Accepted Answer · 2019-06-10 11:58:49Z

1

You can use grouping sets for that, e.g.:

SELECT hour, COUNT(*) FROM hours GROUP BY GROUPING SETS ((hour), ()) 
ORDER BY hour NULLS FIRST

This will return results like:

 hour | count
------+-------
      | 6
   00 | 1
   08 | 2
   16 | 3

answered Jun 10, 2019 at 11:58

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL should return both count and rows within a single statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related