'SPARQL: Variable must be included in group by clause

For every sosa:FeatureOfInterest (room), get the lowest temperature per day from the associated sensors for that room. There are 100 rooms. Each room has 3 sensors. The timeframe is one year.

Goal: Query to select lowest temperature per day per room from group of sensors plus time of day when the temperature occurred.

Example data (N3):

prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns>
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix sosa: <http://www.w3.org/ns/sosa/> 
prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ex: <http://www.example.com/> 

# Room FOIs
ex:room1Foi a sosa:FeatureOfInterest .
# ...
ex:room100Foi a sosa:FeatureOfInterest .

# Room 1 sensor observations 1/1/2021
ex:obs1Room1 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room1Foi ;
    sosa:resultTime "2021-01-01T00:00:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "51.4"^^xsd:decimal .
ex:obs2Room1 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room1Foi .
    sosa:resultTime "2021-01-01T08:00:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "50.2"^^xsd:decimal .
ex:obs3Room1 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room1Foi .
    sosa:resultTime "2021-01-01T:16:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "49.8"^^xsd:decimal .

# Room 1 sensor observations 1/2/2021
ex:obs4Room1 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room1Foi ;
    sosa:resultTime "2021-01-02T00:00:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "61.4"^^xsd:decimal .
ex:obs5Room1 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room1Foi .
    sosa:resultTime "2021-01-02T08:00:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "60.2"^^xsd:decimal .
ex:obs6Room1 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room1Foi .
    sosa:resultTime "2021-01-02T:16:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "59.8"^^xsd:decimal .

# ...

# Room 100 sensor observations 1/1/2021
ex:obs1Room100 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room100Foi ;
    sosa:resultTime "2021-01-01T00:00:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "50.7"^^xsd:decimal .
ex:obs2Room100 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room100Foi .
    sosa:resultTime "2021-01-01T08:00:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "51.6"^^xsd:decimal .
ex:obs3Room100 a sosa:Observation .
    sosa:hasFeatureOfInterest ex:room100Foi .
    sosa:resultTime "2021-01-01T:16:00"^^xsd:dateTime ;
    sosa:observedProperty "TEMP"^^xsd:string ;
    sosa:hasSimpleResult "48.0"^^xsd:decimal .

# Room 1 sensor observations 1/2/2021

# ...

One attempt:

prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns>
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix sosa: <http://www.w3.org/ns/sosa/> 
prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ex: <http://www.example1.com/> 
select ?oFoi ?day min(?val) as ?minTemp ?time where {
 {select ?f where {
  ?f a sosa:FeatureOfInterest .
 }}
 ?o a sosa:Observation ;
  sosa:hasFeatureOfInterest ?oFoi ;
  sosa:resultTime ?time ;
  sosa:observedProperty ?p ;
  sosa:hasSimpleResult ?val .
 filter(?oFoi = ?f) .
 bind(day(?t) as ?day) .
} group by ?oFoi ?day ?time
order by desc(?oFoi) asc(?day)

Result:

oFoi	day	minTemp	time
http://www.example.com/room1Foi	1	51.4	`2021-01-01 0:00:00`
http://www.example.com/room1Foi	1	50.2	`2021-01-01 8:00:00`
http://www.example.com/room1Foi	1	49.8	`2021-01-01 16:00:00`
http://www.example.com/room1Foi	2	59.8	`2021-01-02 16:00:00`
http://www.example.com/room1Foi	2	60.2	`2021-01-02 8:00:00`
http://www.example.com/room1Foi	2	61.4	`2021-01-02 0:00:00`
...	...	...	...

This doesn't work because ?time must be included in the group by clause. Removing ?time from group by clause will return the correct rows. However, ?time is necessary to be included.

Ideal result:

oFoi	day	minTemp	time
http://www.example.com/room1Foi	1	49.8	`2021-01-01 16:00:00`
http://www.example.com/room1Foi	2	59.8	`2021-01-02 16:00:00`
...	...	...	...

Update: This gets closer but still is including multiple results when two times in the same day have the same temperature (both results included):

select ?o2 ?oFoi2 ?day2 ?val2 sample(?t2) as ?tx2 ?p2 where {
  ?o2 a sosa:Observation ;
    sosa:hasFeatureOfInterest ?oFoi2 ;
    sosa:resultTime ?t2 ;
    sosa:observedProperty ?p2 ;
    sosa:hasSimpleResult ?val2 .
  bind(day(?t2) as ?day2) .  
  filter(?oFoi2 = ?oFoi) .
  filter(?day2 = ?day) . 
  filter(?val2 = ?vx) . 

  {select ?oFoi ?day min(?val) as ?vx where {
      {select ?f where {
        ?f a sosa:FeatureOfInterest .
      }}
      ?o a sosa:Observation ;
        sosa:hasFeatureOfInterest ?oFoi ;
        sosa:resultTime ?t ;
        sosa:observedProperty ?p ;
        sosa:hasSimpleResult ?val .
      filter(?oFoi = ?f) .
      bind(day(?t) as ?day) .
    } group by ?oFoi ?day 
    order by desc(?oFoi) asc(?day)
  }

} group by ?o2 ?oFoi2 ?day2 ?p2 ?val2

Result:

o2	oFoi2	day2	val2	tx2	p2
http://www.example3.com/obs3Room100	http://www.example3.com/room100Foi	1	48	`2021-01-01 16:00:00`	"TEMP"^^http://www.w3.org/2001/XMLSchema#string
http://www.example3.com/obs6Room1	http://www.example3.com/room1Foi	2	59.8	`2021-01-02 16:00:00`	"TEMP"^^http://www.w3.org/2001/XMLSchema#string
http://www.example3.com/obs333Room1	http://www.example3.com/room1Foi	1	-9.8	`2021-01-01 16:00:00`	"aTEMP"^^http://www.w3.org/2001/XMLSchema#string
http://www.example3.com/obs33Room1	http://www.example3.com/room1Foi	1	-9.8	`2021-01-01 7:59:00`	"aTEMP"^^http://www.w3.org/2001/XMLSchema#string
...	...	...	...	...	...

Oops: ?o2 is unnecessary and removing it from the above query results in the correct solution.

sparql rdf

Solution 1:^[1]

Adding an outer select appears to solve this. Using an aggregate for the time such as min(), max(), avg(), sample(), etc. are all valid approaches for aggregating the time variable. (Adding an extra property aTEMP to help in understanding.)

Query:

select ?oFoi2 ?day2 ?val2 sample(?t2) as ?tx2 ?p2 where {
  ?o2 a sosa:Observation ;
    sosa:hasFeatureOfInterest ?oFoi2 ;
    sosa:resultTime ?t2 ;
    sosa:observedProperty ?p2 ;
    sosa:hasSimpleResult ?val2 .
  bind(day(?t2) as ?day2) .  
  filter(?oFoi2 = ?oFoi) .
  filter(?day2 = ?day) . 
  filter(?val2 = ?vx) . 

  {select ?oFoi ?day min(?val) as ?vx where {
      {select ?f where {
        ?f a sosa:FeatureOfInterest .
      }}
      ?o a sosa:Observation ;
        sosa:hasFeatureOfInterest ?oFoi ;
        sosa:resultTime ?t ;
        sosa:observedProperty ?p ;
        sosa:hasSimpleResult ?val .
      filter(?oFoi = ?f) .
      bind(day(?t) as ?day) .
    } group by ?oFoi ?day 
    order by desc(?oFoi) asc(?day)
  }

} group by ?oFoi2 ?day2 ?p2 ?val2

Result:

oFoi2	day2	val2	tx2	p2
http://www.example3.com/room100Foi	1	48	`2021-01-01 16:00:00`	"TEMP"^^http://www.w3.org/2001/XMLSchema#string
http://www.example3.com/room1Foi	2	59.8	`2021-01-02 16:00:00`	"TEMP"^^http://www.w3.org/2001/XMLSchema#string
http://www.example3.com/room1Foi	1	-9.8	`2021-01-01 7:59:00`	"aTEMP"^^http://www.w3.org/2001/XMLSchema#string
...	...	...	...	...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	There

'SPARQL: Variable must be included in group by clause

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]