Database Reference
In-Depth Information
?quarter qb4o:inLevel nw:Quarter ; skos:broader ?sem .
?sem qb4o:inLevel nw:Semester ; skos:broader ?year .
?year qb4o:inLevel nw:Year ; nw:year ?yearNo .
}
GROUP BY ?fName ?lName ?yearNo
ORDER BY ?fName ?lName ?yearNo
In the query above, the inner query computes the total sales amount by
employee and month. The outer query rolls up the previous result to the year
level while computing the total yearly sales and the average monthly sales.
Query 14.9.
Total sales amount and total discount amount per product
and month.
SELECT ?prodName ?yearNo ?monthNo (SUM(?sales) AS ?totalSales)
(SUM(?unitPrice * ?qty * ?disc) AS ?totalDiscAmount)
WHERE
?o qb:dataSet nwi:dataset1 ; nw:Product ?prod ;
nw:OrderDate ?odate ; nw:SalesAmount ?sales ;
nw:Quantity ?qty ; nw:Discount ?disc ; nw:UnitPrice ?unitPrice .
?prod qb4o:inLevel nw:Product ; nw:productName ?prodName .
?odate qb4o:inLevel nw:OrderDate ; skos:broader ?month .
?month qb4o:inLevel nw:Month ; nw:monthNumber ?monthNo ;
skos:broader ?quarter .
?quarter qb4o:inLevel nw:Quarter ; skos:broader ?sem .
?sem qb4o:inLevel nw:Semester ; skos:broader ?year .
?year qb4o:inLevel nw:Year ; nw:year ?yearNo .
}
GROUP BY ?prodName ?yearNo ?monthNo
ORDER BY ?prodName ?yearNo ?monthNo
In this query, we roll up to the month level and then compute the requested
measures.
{
Query 14.10.
Monthly year-to-date sales for each product category.
SELECT ?catName ?yearNo ?monthNo (SUM(?totalSales1) AS ?YTDSales)
WHERE
{
?cat qb4o:inLevel nw:Category ; nw:categoryName ?catName .
?month qb4o:inLevel nw:Month ; nw:monthNumber ?monthNo ;
skos:broader ?quarter .
?quarter qb4o:inLevel nw:Quarter ; skos:broader ?sem .
?sem qb4o:inLevel nw:Semester ; skos:broader ?year .
?year qb4o:inLevel nw:Year ; nw:year ?yearNo.
{
SELECT ?catName ?yearNo ?monthNo1 (SUM(?sales1) AS ?totalSales1)
WHERE
{
?o1 qb:dataSet nwi:dataset1 ; nw:Product ?prod1 ;
nw:OrderDate ?odate1 ; nw:SalesAmount ?sales1 .
?prod1 qb4o:inLevel nw:Product ; skos:broader ?cat1 .
?cat1 qb4o:inLevel nw:Category ; nw:categoryName ?catName .
?odate1 qb4o:inLevel nw:OrderDate ; skos:broader ?month1 .
?month1 qb4o:inLevel nw:Month ; nw:monthNumber ?monthNo1 ;
skos:broader ?quarter1 .
?quarter1 qb4o:inLevel nw:Quarter ; skos:broader ?sem1 .
?sem1 qb4o:inLevel nw:Semester ; skos:broader ?year1 .
?year1 qb4o:inLevel nw:Year ; nw:year ?yearNo.
}
GROUP BY ?catName ?yearNo ?monthNo1
}
FILTER( ?monthNo
>
=
?monthNo1 )
}
GROUP BY ?catName ?yearNo ?monthNo
ORDER BY ?catName ?yearNo ?monthNo
Search WWH ::
Custom Search