ERROR: aggregate function calls cannot be nested - POSTGRESQL

Question

I am trying to create a select with record history within 30 days, where the values column gets a count, I just wish it would add the values returned for each day.

QUERY

SELECT
    date_update AS Time,
    SUM(COUNT(values)) as "Value"
FROM
    tb_get_metrics
WHERE
    data_update >= CURRENT_DATE - 30
GROUP BY Time
ORDER BY Time

Example OUTPUT:

       Time                      Value
2019-10-14 09:46:54.789772        30
2019-10-15 09:46:54.789772        50
2019-10-16 09:46:54.789772        70

SELECT * FROM tb_get_metrics

  date_update(TimeStamp)        value(String)
2019-10-14 09:46:54.789772        apple
2019-10-14 09:46:55.789772        apple
2019-10-14 09:46:56.789772        apple
2019-10-14 09:46:57.789772        apple
2019-10-14 09:46:58.789772        apple
2019-10-14 09:46:59.789772        apple
2019-10-14 09:47:00.789772        apple
2019-10-14 09:46:01.789772        apple
2019-10-14 09:46:02.789772        apple
2019-10-14 09:46:03.789772        apple
2019-10-14 09:46:04.789772        apple
2019-10-14 09:46:05.789772        apple
2019-10-15 09:46:03.789772        potato
2019-10-15 09:46:04.789772        potato
2019-10-15 09:46:05.789772        potato
...

Because the values are strings, I want to count how many there are on that day — Luis Henrique
– Luis Henrique, Commented Oct 15, 2019 at 18:02
could you update the post with what SELECT * FROM tb_get_metrics would look like? — richyen
– richyen, Commented Oct 15, 2019 at 18:04

klin · Accepted Answer · 2019-10-15 18:13:27Z

1

You need to round the times to dates:

SELECT
    date_update::date AS "Date",
    COUNT(values) as "Value"
FROM
    tb_get_metrics
WHERE
    data_update >= CURRENT_DATE - 30
GROUP BY Time
ORDER BY Time

answered Oct 15, 2019 at 18:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

forpas · Accepted Answer · 2019-10-15 18:13:39Z

1

Get the date part only from each timestamp to group by:

SELECT
    date_update::date AS Time,
    COUNT(values) AS "Value"
FROM tb_get_metrics
WHERE date_update >= CURRENT_DATE - 30
GROUP BY Time
ORDER BY Time

answered Oct 15, 2019 at 18:13

forpas

165k10 gold badges51 silver badges85 bronze badges

1 Comment

Luis Henrique Over a year ago

Thank you friend, your solution is functional !!

richyen · Accepted Answer · 2019-10-15 18:23:37Z

1

By truncating the times off the timestamps, you can focus only on the date, which is what your WHERE clause seeks to do:

postgres=# SELECT
    date_update::date AS "Date",
    COUNT(values) as "Count"
FROM
    tb_get_metrics
WHERE
    date_update::date >= CURRENT_DATE - 30
GROUP BY "Date"           
ORDER BY "Date";
    Date    | Count 
------------+-------
 2019-10-14 |    24
 2019-10-15 |     6
(2 rows)

answered Oct 15, 2019 at 18:23

richyen

10.3k4 gold badges18 silver badges36 bronze badges

1 Comment

Luis Henrique Over a year ago

Perfect friend thank you so much for your effort to solve the problem !!

Collectives™ on Stack Overflow

ERROR: aggregate function calls cannot be nested - POSTGRESQL

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related