Percentage from two COUNTs

Question

I have this sparql query:

SELECT DISTINCT (COUNT(?bw) AS ?total) (COUNT(?bw_bad) AS ?total_bad) WHERE
{ 
{
    SELECT ?bw WHERE 
    {
         ?bw unt:has_bwid ?id
    }
}
UNION
{
    SELECT ?bw_bad WHERE 
    {
      ?bw_bad unt:has_rbdname ?rbd_name_bad .
      ?bw_bad unt:has_concie_0 ?concie_0 .
      FILTER(?concie_0 > 40)
    }
}
}

which gives:

total                                              total_bad
"2155"^^<http://www.w3.org/2001/XMLSchema#integer>  "46"^^<http://www.w3.org/2001/XMLSchema#integer>

I would like to calculate the percentage of them, which would give (46 / 2155 * 100) 2.13%. How can I do it? I do not care about performance.

My attempt:

SELECT ((COUNT(?bw_bad) AS ?total_bad)/(COUNT(?bw) AS ?total)*100) WHERE

which gives this syntax error:

Encountered " "as" "AS "" at line 10, column 34. Was expecting one of: ")" ... "=" ... "!=" ... ">" ... "<" ... "<=" ... ">=" ... "||" ... "&&" ... "+" ... "-" ... "*" ... "/" ... "in" ... "not in" ... ... ... ... ... ... ...

I just thout that [tag:sql] guys may come to the rescue @JeenBroekstra. I will think about another tag and replace it! — gsamaras, Apr 09 '16 at 23:10
That's not how tags work. It's not a "give me attention please" mechanism. They're supposed to actually indicate what the question is about. — Jeen Broekstra, Apr 09 '16 at 23:13
I agree @JeenBroekstra, my bad. But that was not the intention, I thought that these two languages were pretty close. :) — gsamaras, Apr 09 '16 at 23:14
No need to trouble the sql people, as you can see there's plenty of sparql people - three near-identical answers :) — Jeen Broekstra, Apr 09 '16 at 23:22

Jeen Broekstra · Answer 1 · 2016-04-09T23:57:55.403

2

The problem is twofold: you're assigning the intermediate outcome of your aggregates to variables during computation, and then you actually don't assign the final outcome to a variable. You should instead do something like this:

SELECT (COUNT(?bw_bad)/(COUNT(?bw)*100) as ?percentage)

edited Apr 09 '16 at 23:57

answered Apr 09 '16 at 23:15

Jeen Broekstra

21,642
4
51
73

I am getting an error: `Encountered " ")" ") "" at line 10, column 40. Was expecting one of: "=" ... "!=" ... ">" ... "<" ... "<=" ... ">=" ... "||" ... "&&" ... "+" ... "-" ... "*" ... "/" ... "as" ... "in" ... "not in" ... ... ... ... ... ... ... ` – gsamaras Apr 09 '16 at 23:23
The difference I see with the answer of @Mariei is that you placed the `100` in another position. @scotthenninger has it an another position and manages to get a result, but it's not the expected one. – gsamaras Apr 09 '16 at 23:27
I placed the 100 in the position you indicated it should go. I didn't actually check that your calculation was correct, I merely fixed your syntax. As for the syntax error: I made a small booboo with the parentheses. Fixed now. – Jeen Broekstra Apr 09 '16 at 23:56
Thought so Jeen! Works now, I wonder if I could get a floating point result instead of `2`. Do you know if SPARQL supports casting or something? – gsamaras Apr 10 '16 at 00:02
1

It does. `xsd:float(integer)` casts an integer to a float. – Jeen Broekstra Apr 10 '16 at 00:03

score 2 · Accepted Answer · edited Apr 09 '16 at 23:31

2

Change this:

SELECT ((COUNT(?bw_bad) AS ?total_bad)/(COUNT(?bw) AS ?total)*100) WHERE

to this:

SELECT (COUNT(?bw_bad)* 100 / (COUNT(?bw)) as ?total) WHERE

edited Apr 09 '16 at 23:31

gsamaras

71,951
46
188
305

answered Apr 09 '16 at 23:16

Marievi

4,951
1
16
33

That works! I get an output of `2`, can we get it in as a floating point or am I asking for too much? – gsamaras Apr 09 '16 at 23:23
@Jeen Broekstra answered that: `xsd:float(integer)`. – gsamaras Apr 10 '16 at 00:05

score 1 · Answer 3 · answered Apr 09 '16 at 23:18

1

The AS in your select is assigning a name to the aggregate result. Since you are using the aggregates in the computation, there is no need to name them, and that's why the syntax is not allowed.

The use of sub-selects is almost always a mistake, unless they are being used to compute an aggregate needed by the surrounding query. You're not doing that in this case and therefore you can remove the sub-selects for a more efficient query:

SELECT (((COUNT(?bw_bad)/COUNT(?bw))*100) AS ?total_bad)
WHERE
{ 
  {
     ?bw unt:has_bwid ?id .
  }
  UNION
  {
    ?bw_bad unt:has_rbdname ?rbd_name_bad .
    ?bw_bad unt:has_concie_0 ?concie_0 .
    FILTER(?concie_0 > 40)
  }
}

answered Apr 09 '16 at 23:18

scotthenninger

3,921
1
15
24

This gives me `0` as a result. The match is a bit wrong, isn't it? :) – gsamaras Apr 09 '16 at 23:25
The match is correct and the equivalent of what you have with the sub-selects. Differences in the syntax of the aggregates could depend on SPARQL engine. For Jena, mine and the one from @Marievi give the same result (mathematically it has to), just using different variable names for the project variable. Check the query with `SELECT (COUNT(?bw) AS ?total) (COUNT(?bw_bad) AS ?total_bad)`, and I you'll find the match is the same with or without the sub-selects. – scotthenninger Apr 10 '16 at 04:45

Percentage from two COUNTs

3 Answers3