Submit Feedback/Bug Report

myExperiment Logo How To SPARQL

                    Back to Contents Page

6. GROUP BY

The purpose of the GROUP BY clause is to allow aggregation over one or more properties. This is particularly useful when you want to use mathematical functions on variables in the SELECT clause. A good example is using COUNT to list how many workflows are owned by each user.

PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX mecontrib: <http://rdf.myexperiment.org/ontologies/contributions/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT ?user (COUNT(?workflow) AS ?no_workflows)
WHERE{
  ?workflow rdf:type mecontrib:Workflow .
  ?workflow sioc:has_owner ?user .
}
GROUP BY ?user
[Run]
[Hide Example Results]
userno_workflows
http://www.myexperiment.org/users/68903
http://www.myexperiment.org/users/362
http://www.myexperiment.org/users/101731
http://www.myexperiment.org/users/8842
http://www.myexperiment.org/users/871
http://www.myexperiment.org/users/86741
http://www.myexperiment.org/users/1283512
http://www.myexperiment.org/users/74861
http://www.myexperiment.org/users/453311
http://www.myexperiment.org/users/831

The GROUP BY clause can also be used with the SUM function to for example get the total number of downloads for all the workflows owned by each user.

PREFIX mecontrib: 
PREFIX mevd: 
PREFIX sioc: 
PREFIX rdf: 
SELECT ?user (SUM(?downloaded) AS ?total_downloads)
WHERE{
  ?workflow rdf:type mecontrib:Workflow ;
    sioc:has_owner ?user ;
    mevd:downloaded ?downloaded
}
GROUP BY ?user
[Run]
[Hide Example Results]
usertotal_downloads
http://www.myexperiment.org/users/6890351
http://www.myexperiment.org/users/36520
http://www.myexperiment.org/users/10173359
http://www.myexperiment.org/users/884792
http://www.myexperiment.org/users/87667
http://www.myexperiment.org/users/867460

Again, the GROUP BY clause can also be used with the AVG, MAX and MIN functions to get for example the average, maximum and minium ratings of workflows.

PREFIX mebase: <http://rdf.myexperiment.org/ontologies/base/>
PREFIX meannot: <http://rdf.myexperiment.org/ontologies/annotations/>
PREFIX mecontrib: <http://rdf.myexperiment.org/ontologies/contributions/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT ?workflow (AVG(?rating_score) AS ?avg_rating) (MAX(?rating_score) AS ?max_rating) 
  (MIN(?rating_score) AS ?min_rating)
WHERE {
  ?workflow rdf:type mecontrib:Workflow .
  ?rating rdf:type meannot:Rating ;
    mebase:annotates ?workflow ;
    meannot:rating-score ?rating_score
}
GROUP BY ?workflow
[Run]
[Hide Example Results]
>
workflowavg_ratingmax_ratingmin_rating
http://www.myexperiment.org/workflows/240555
http://www.myexperiment.org/workflows/7614.99999999999999999955
http://www.myexperiment.org/workflows/1402444
http://www.myexperiment.org/workflows/203.999999999999999999253
http://www.myexperiment.org/workflows/64.499999999999999999154
http://www.myexperiment.org/workflows/688444

N.B. Currently (March 9th 2011) 4Store does not properly handle floating point numbers properly so average results may need to be manually rounded to make for example 4.4999999999999999991 into 4.5.


                    Back to Contents Page