CODEWITHSUNDEEP
sqlpostgresqlgrafana
user16773013
user16773013

Reputation:

PostgreSQL agregation function with group by

I try to get output from PostgreSQL into Grafana , but there is error

db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function

This is the code

SELECT 
  $__timeGroupAlias(receivedat,$__interval),
  SUBSTRING(message, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}') AS the_address,
  COUNT(message) as cc 
FROM systemevents 
WHERE
  $__timeFilter(receivedat,$__interval)
GROUP BY the_address 
ORDER BY cc DESC

How to solve this problem?

Upvotes: 0

Views: 65

Answers (1)

Jan Garaj
Jan Garaj

Reputation: 28696

Add it to the GROUP, e.g. (1 refers to first column from the SELECT):

SELECT 
  $__timeGroupAlias(receivedat,$__interval),
  SUBSTRING(message, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}') AS the_address,
  COUNT(message) as cc 
FROM systemevents 
WHERE
  $__timeFilter(receivedat,$__interval)
GROUP BY 1, the_address 
ORDER BY cc DESC

Upvotes: 1

Related Questions