# Query to count values per day

**URL:** <https://community.influxdata.com/t/query-to-count-values-per-day/24498>\
**Category:** Fluxlang\
**Tags:** influxdb, time, query, flux\
**Created:** [April 1, 2022, 1:35pm UTC](https://community.influxdata.com/t/query-to-count-values-per-day/24498 "2022-04-01T13:35:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rucaza](https://avatars.discourse-cdn.com/v4/letter/r/76d3ee/32.png) [@rucaza](https://community.influxdata.com/u/rucaza)\
**Post date:** [April 1, 2022, 1:35pm UTC](https://community.influxdata.com/t/query-to-count-values-per-day/24498/1 "2022-04-01T13:35:55Z")

</div>

I created a query that will count the number of occurrences of the Offset field in a range of four days aggregated day by day:

```auto
from(bucket: "my_bucket")
	|> range(start: -4d, stop: today())
	|> filter(fn: (r) => 
		r[\"_measurement\"] == "measurement1" 
		and r[\"SuitId\"] == "xxxyyyzzzvvv"
		and r._field == "Offset"
	)
	|> group(columns: ["_start"])
	|> aggregateWindow(every: 1d, fn: count)
	|> sort(columns: ["_time"], desc: true)"

```

the result i get is something like this

```auto
_value=90010 _time=today   
_value=50 _time=yesterday    
_value=0 _time=the day before

```

Witch looks fabulous, except the days are not matching the data. In the table the 90010 corresponds to yesterday and 50 corresponds to the day before. I don’t understand what i’m doing wrong. Can someone point at where i’m wrong. many thanks.

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [April 5, 2022, 3:14pm UTC](https://community.influxdata.com/t/query-to-count-values-per-day/24498/2 "2022-04-05T15:14:42Z")

</div>

@rucaza When you use `aggregateWindow()`, the aggregate function (`count`) actually drops the `_time` column because it isn’t part of the group key and it doesn’t know how to aggregate the time value. By default `aggregateWindow()` duplicates the `_stop` column as the new `_time` column to give each row a `_time` value for the window the row represents.

You can control which column `aggregateWindow()` duplicates as the output time with the `timeSrc` parameter. In your case, I’d suggest setting `timeSrc: "_start"`:

```javascript
// ...
    |> aggregateWindow(every: 1d, fn: count, timeSrc: "_start")
// ...

```

---

<div class="post-metadata">

**Author:** ![joxafa](https://avatars.discourse-cdn.com/v4/letter/j/57b2e6/32.png) [@joxafa](https://community.influxdata.com/u/joxafa)\
**Post date:** [March 7, 2023, 12:00pm UTC](https://community.influxdata.com/t/query-to-count-values-per-day/24498/3 "2023-03-07T12:00:45Z")

</div>

Hello All,

I am writing to ask how I might create a SQL query to obtain a count of the number of records for each day “column1” has a value of “value1”

The output I would like to see in each row has two columns e.g [holiday counting](https://contagemdedias.com.br/), the date, and then the number of times “value1” appeared in “column1” that for that day. Any help is greatly appreciated.

Thanks in advance,