influxdbinfluxql

influxdb count() gives wrong value


I have Influx DB where I store information related jenkins.

when I execute when below query

SELECT
  project_name,
  build_number,
  build_result
FROM (
  SELECT
    project_name,
    build_number,
    build_result
  FROM
    "jenkins_data"
  WHERE (
    "project_name" =~ /^(?i)(test1|test2)$/ AND
    "project_path" =~ /.*(?i)Playground.*$/
  )
  ORDER BY time DESC
  LIMIT 15
)
WHERE (
  "build_result" = 'SUCCESS'
)
ORDER BY time DESC

will get the below result

time                project_name         build_number build_result

1676039543717000000 test1                1600         SUCCESS
1676039352721000000 test1                1792         SUCCESS
1676039283509000000 test2                1669         SUCCESS
1676039543717000000 test1                1600         SUCCESS
1676039352721000000 test1                1792         SUCCESS
1676039283509000000 test2                1669         SUCCESS
1676039543717000000 test1                1600         SUCCESS
1676039352721000000 test1                1792         SUCCESS
1676039283509000000 test2                1669         SUCCESS
1676039283509000000 test2                1669         SUCCESS

the above result is correct but when use count in my query it gives improper results

SELECT
  count(build_number)
FROM (
  SELECT
    project_name,
    build_number,
    build_result
  FROM
    "jenkins_data"
  WHERE (
    "project_name" =~ /^(?i)(test1|test2)$/ AND
    "project_path" =~ /.*(?i)Playground.*$/
  )
  ORDER BY time DESC
  LIMIT 15
)
WHERE (
  "build_result" = 'SUCCESS'
)
ORDER BY time DESC

will give result as 15 which is not correct I am doing anything wrong here?

Influxdb version used - InfluxDB v1.8.6 (git: 1.8 v1.8.6)


Solution

  • I could able to solve the issue by adding DISTINCT in count functions.

    used below query

    SELECT
      count(DISTINCT build_number)
    FROM (
      SELECT
        project_name,
        build_number,
        build_result
      FROM
        "jenkins_data"
      WHERE (
        "project_name" =~ /^(?i)(test1|test2)$/ AND
        "project_path" =~ /.*(?i)Playground.*$/
      )
      ORDER BY time DESC
      LIMIT 15
    )
    WHERE (
      "build_result" = 'SUCCESS'
    )
    ORDER BY time DESC