N1ql Group By on 2 attributes

Couchbase Server
1

I am trying to use group by on 2 attributes but its not showing me the required result.

Here is my query

SELECT COUNT(ins.qty) as qty ,MIN(ins.service_id) as  id,MIN(inv.branch_name) as  storeName, MIN(inv.code) as  code, MIN(ins.service_name) as  name, MIN(inv.date) as  date, SUM(ins.total_bill) as total_amount FROM data_bucket AS inv USE INDEX (def_cus_id_invoice)
		UNNEST inv.invoice_services AS ins
		WHERE inv.cus_id = '006019000015'
		AND inv.type = 'invoice' GROUP BY ins.service_id AND inv.code

I want to group by services used by customer is each store and sum there total services .

The above query result is

[
{
    "code": "3221",
    "date": "2021-02-03",
    "id": "3221190002",
    "name": "Dressess",
    "qty": 146,
    "storeName": "Regent Mall",
    "total_amount": 3727.230000000001
}
]

The result is showing only 1 record instead there are multi records of multiple store for same customer.

2

Checkout your gorup by expression is right

GROUP BY ins.service_id AND inv.code

Above condition means True, False. If you need multiple expression in group by you should use comma

GROUP BY ins.service_id, inv.code

1 Like