Database Reference
In-Depth Information
# Minimum cumulative sales
>
=
50% of the overall sales
{
SELECT (MIN(?cumSales2) AS ?threshold)
WHERE
{
# 50% of the overall sales
{
SELECT (0.5 * SUM(?sales) AS ?halfOverallSales)
WHERE
{
?o qb:dataSet nwi:dataset1 ;
nw:SalesAmount ?sales .
}}
# Total sales and cumulative sales by state
{
SELECT ?state2 ?totalSales2
(SUM(?totalSales3) AS ?cumSales2)
WHERE
{
{
SELECT ?state2 (SUM(?sales2) AS ?totalSales2)
WHERE
?o2 qb:dataSet nwi:dataset1 ;
nw:Customer ?cust2 ; nw:SalesAmount ?sales2 .
?cust2 qb4o:inLevel nw:Customer ; skos:broader ?city2 .
?city2 qb4o:inLevel nw:City ; skos:broader ?state2 .
?state2 qb4o:inLevel nw:State .
}
GROUP BY ?state2
}
{
SELECT ?state3 (SUM(?sales3) AS ?totalSales3)
WHERE
{
?o3 qb:dataSet nwi:dataset1 ;
nw:Customer ?cust3 ; nw:SalesAmount ?sales3 .
?cust3 qb4o:inLevel nw:Customer ; skos:broader ?city3 .
?city3 qb4o:inLevel nw:City ; skos:broader ?state3 .
?state3 qb4o:inLevel nw:State .
}
GROUP BY ?state3
}
FILTER ( ?totalSales2
<
=
?totalSales3 )
}
GROUP BY ?state2 ?totalSales2
}
FILTER(?cumSales2
>
=
?halfOverallSales)
}}
FILTER(?cumSales
<
=
?threshold)
}
ORDER BY DESC(?totalSales)
{
The first inner query computes for each country the total sales and the
cumulative sales of all countries having total sales greater than or equal to
the total sales of the country. The second inner query computes the threshold
value, which represents the minimum cumulative sales greater than or equal
to the 50% of the overall sales. Finally, the
FILTER
selects all countries whose
cumulative sales are less than or equal to the threshold value.
Query 14.8.
Total sales and average monthly sales by employee and year.
SELECT ?fName ?lName ?yearNo (SUM(?monthlySales) AS ?totalSales)
(AVG(?monthlySales) AS ?avgMonthlySales)
WHERE
{
# Monthly sales by employee
{
SELECT ?fName ?lName ?month (SUM(?sales) AS ?monthlySales)
WHERE
?o qb:dataSet nwi:dataset1 ; nw:Employee ?emp ;
nw:OrderDate ?odate ; nw:SalesAmount ?sales .
?emp qb4o:inLevel nw:Employee ; nw:firstName ?fName ;
nw:lastName ?lName .
?odate qb4o:inLevel nw:OrderDate ; skos:broader ?month .
?month qb4o:inLevel nw:Month .
}
GROUP BY ?fName ?lName ?month
}
?month skos:broader ?quarter .
{
Search WWH ::
Custom Search