You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jena.apache.org by Cindy A McMullen <ci...@oracle.com> on 2014/03/21 18:56:01 UTC
Issues with COUNT
I have a query like this: (assume prefixes are defined)
"SELECT DISTINCT ?opportunity ?status " +
"WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
" ?opportunity w:status ?status ." +
"}";
That returns expected values:
[{opportunity=http://www.oracle.com/osn/osn.owl#Opportunity41, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity40, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity30, status=Lost^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity48, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity6, status=Lost^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity18, status=Lost^^http://www.w3.org/2001/XMLSchema#string}]
What I'd like to do is to count the number of 'Lost' and 'Open' values for this query, so I have this:
"SELECT ?status (count (?status) as ?scount) " +
"WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
" ?opportunity w:status ?status ." +
"} GROUP BY ?status";
But the counts are wrong:
[{status=Lost^^http://www.w3.org/2001/XMLSchema#string, scount=5^^http://www.w3.org/2001/XMLSchema#integer}, {status=Open^^http://www.w3.org/2001/XMLSchema#string, scount=7^^http://www.w3.org/2001/XMLSchema#integer}]
-----
How can I fix the above query to return the correct counts?
Thanks -
-- Cindy
Re: Issues with COUNT
Posted by Andy Seaborne <an...@apache.org>.
On 21/03/14 17:56, Cindy A McMullen wrote:
> I have a query like this: (assume prefixes are defined)
> "SELECT DISTINCT ?opportunity ?status " +
> "WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
> " ?opportunity w:status ?status ." +
> "}";
>
> That returns expected values:
> [{opportunity=http://www.oracle.com/osn/osn.owl#Opportunity41, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity40, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity30, status=Lost^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity48, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity6, status=Lost^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity18, status=Lost^^http://www.w3.org/2001/XMLSchema#string}]
>
> What I'd like to do is to count the number of 'Lost' and 'Open' values for this query, so I have this:
>
> "SELECT ?status (count (?status) as ?scount) " +
> "WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
> " ?opportunity w:status ?status ." +
> "} GROUP BY ?status";
>
> But the counts are wrong:
In what way?
You have a DISTINCT over ?opportunity ?status in the first query. Rows
may have been removed.
>
> [{status=Lost^^http://www.w3.org/2001/XMLSchema#string, scount=5^^http://www.w3.org/2001/XMLSchema#integer}, {status=Open^^http://www.w3.org/2001/XMLSchema#string, scount=7^^http://www.w3.org/2001/XMLSchema#integer}]
>
> -----
>
> How can I fix the above query to return the correct counts?
COUNT(DISTINCT ...)
but it is DISTINCT over the COUNT expression, not the pairs
(?opportunity, ?status)
What is
SELECT ?opportunity ?status (count (distinct *) AS ?c) {
...}
GROUP BY ?opportunity ?status
Andy
>
> Thanks -
>
> -- Cindy
>
Re: Issues with COUNT
Posted by Cindy A McMullen <ci...@oracle.com>.
Perfect, James, thanks for the example! Here's the query that works:
SELECT ?status (count(?status) as ?count) WHERE {" +
"SELECT DISTINCT ?opportunity ?status " +
"WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
" ?opportunity w:status ?status ." +
"} } GROUP BY ?status";
On Mar 21, 2014, at 1:21 PM, james anderson wrote:
> good evening;
>
>
> On 21 Mar 2014, at 20:11, Cindy A McMullen <ci...@oracle.com> wrote:
>
>> Thanks, James. Can you give an example?
>
> look here
>
> http://dydra.com/jhacker/foaf/sparql#subquery
>
> the dataset content is not meaningfull wrt the query, but it gives the idea.
>
>>
>> On Mar 21, 2014, at 1:00 PM, james anderson wrote:
>>
>>> try a subselect with the distinct feeding into the outer select with the aggregation.
>>>
>>> On 21 Mar 2014, at 18:56, Cindy A McMullen <ci...@oracle.com> wrote:
>>>
>>>> I have a query like this: (assume prefixes are defined)
>>>> "SELECT DISTINCT ?opportunity ?status " +
>>>> "WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
>>>> " ?opportunity w:status ?status ." +
>>>> "}";
>>>>
>>>> That returns expected values:
>>>> [{opportunity=http://www.oracle.com/osn/osn.owl#Opportunity41, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity40, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity30, status=Lost^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity48, status=Open^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity6, status=Lost^^http://www.w3.org/2001/XMLSchema#string}, {opportunity=http://www.oracle.com/osn/osn.owl#Opportunity18, status=Lost^^http://www.w3.org/2001/XMLSchema#string}]
>>>>
>>>> What I'd like to do is to count the number of 'Lost' and 'Open' values for this query, so I have this:
>>>>
>>>> "SELECT ?status (count (?status) as ?scount) " +
>>>> "WHERE { ?u w:role \"Sales Rep\" . ?u w:participatedIn ?opportunity . ?opportunity w:customer \"IBM\" . " +
>>>> " ?opportunity w:status ?status ." +
>>>> "} GROUP BY ?status";
>>>>
>>>> But the counts are wrong:
>>>>
>>>> [{status=Lost^^http://www.w3.org/2001/XMLSchema#string, scount=5^^http://www.w3.org/2001/XMLSchema#integer}, {status=Open^^http://www.w3.org/2001/XMLSchema#string, scount=7^^http://www.w3.org/2001/XMLSchema#integer}]
>>>>
>>>> -----
>>>>
>>>> How can I fix the above query to return the correct counts?
>>>>
>>>> Thanks -
>>>>
>>>> -- Cindy
>>>
>>> ---
>>> james anderson | james@dydra.com | http://dydra.com
>>>
>>>
>>>
>>>
>>>
>>
>
> ---
> james anderson | james@dydra.com | http://dydra.com
>
>
>
>
>