Search code examples
sqlpostgresqlaggregate-functions

ERROR: aggregate function calls cannot be nested - POSTGRESQL

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
...
Solution

  • 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