Sum with groupBy in CBL with Join

N1QL
1

I have this documents (cut out for simplification):

{"Type" : "Book", "BookId" : 920, "Cost": 2000, "TypeId": 1, "_id" : "Book_1_920"},     
{"Type" : "Book", "BookId" : 921, "Cost" : 1800, "TypeId": 1, "_id" : "Book_1_921"},
{"Type" : "Rate", "BookId" : 920, "RateId":1, "Rating": 3, "TypeId": 1, "_id" : "Rate_1_920_1"},     
{"Type" : "Rate", "BookId" : 920, "RateId":2, "Rating": 5, "TypeId": 1, "_id" : "Rate_1_920_2"}

I want to create an aggregate by TypeId so the final output should be something like this:

{TypeId: 1, sumRatings: 8, costofBooksTotal: 3800, totalBooksCount: 2}

I am doing the following but first the numbers are wait out of line and secondly, I can’t seem to get TypeId (it returns null). It works fine if the queries are separated but I am hoping to do a Join:

Query query = QueryBuilder.select(
        SelectResult.expression(Function.sum(Expression.property("Cost").from("books"))),
        SelectResult.expression(Function.sum(Expression.property("Rating").from("ratings"))),
        SelectResult.expression(Expression.property("TypeId").from("books"))
       )
        .from(DataSource.database(this.database).as("books"))
        .join(Join.join(DataSource.database(this.database).as("ratings"))
                .on(Expression.property("TypeId").from("books").equalTo(Expression.property("TypeId").from("ratings"))))
        .where(Expression.property("Type").from("books").equalTo(Expression.string("Book"))
                .and(Expression.property("Type").from("ratings").equalTo(Expression.string("Rating")))
                
                ).groupBy(Expression.property("TypeId").from("books"));

in my output, I get TypeId as null and the sum values way out of line.

2

First you need to check your data and query if that is right. try in Query workbench Console and translate into CBL.

SELECT b.TypeId, 
       SUM(b.Cost) AS costofBooksTotal, 
       COUNT(1) AS totalBooksCount,
       SUM(r.Rating) AS sumRatings
FROM bucket AS b
LEFT JOIN bucket AS r ON b.BookId = r.BookId AND b.TypeId = r.TypeId
WHERE b.Type = "Book" AND r.Type = "Rate"
GROUP BY b.TypeId;

If Mobile ignore this and change category to Mobile

3

Thank you for the tip. I did that and got wrong results but I know where the numbers are coming but not sure how to fix it at all. So running the above query yields the following:

costofBooksTotal = correctCost * numberOfRatings
sumRatings = correntRatingsTotal * numberofBooks

any idea?

4 
INSERT INTO default VALUES ("Book_1_920",{"Type" : "Book", "BookId" : 920, "Cost": 2000, "TypeId": 1, "_id" : "Book_1_920"});
INSERT INTO default VALUES ("Book_1_921",{"Type" : "Book", "BookId" : 921, "Cost" : 1800, "TypeId": 1, "_id" : "Book_1_921"});
INSERT INTO default VALUES ("Rate_1_920_1", {"Type" : "Rate", "BookId" : 920, "RateId":1, "Rating": 3, "TypeId": 1, "_id" : "Rate_1_920_1"});
INSERT INTO default VALUES ("Rate_1_920_2", {"Type" : "Rate", "BookId" : 920, "RateId":2, "Rating": 5, "TypeId": 1, "_id" : "Rate_1_920_2"});
SELECT d.TypeId,
       SUM(d.cost) AS costofBooksTotal,
       SUM(d.cnt) AS totalBooksCount,
       SUM(d.ratings) AS sumRatings
FROM (SELECT b.TypeId, b.BookId,
         AVG(b.Cost) AS cost,
         COUNT(DISTINCT META(b).id) AS cnt,
         SUM(r.Rating) AS ratings
     FROM default AS b
     LEFT JOIN default AS r ON b.TypeId = r.TypeId AND r.Type = "Rate" AND b.BookId = r.BookId
     WHERE b.Type = "Book"
     GROUP BY b.TypeId, b.BookId) AS d
GROUP BY d.TypeId;

If need AVG ratings use both queries AVG (first one does each typeId, book next one does avg on typeId)