Reputation: 499
I am trying to build big query where I want to list all super market branches in the city and then all the departments in that super market like grocery, cosmetics. List sales for each department and finally calculate total sale in each department. So my response should look something like below
{
"Response": [
{
"branch": 1,
"BranchSaleList": [
{
"department": "Grocery",
"saleDetails": [
{
"item": "rice",
"totalAmt": 100
},
{
"item": "wheat",
"totalAmt": 50
}
],
"totalSale": 150
},
{
"department": "cosmetics",
"saleDetails": [
{
"item": "cream",
"totalAmt": 100
},
{
"item": "powder",
"totalAmt": 100
}
],
"totalSale": 200
}
]
},
{
"branch": 2,
"BranchSaleList": [
{
"department": "Grocery",
"saleDetails": [
{
"item": "rice",
"totalAmt": 200
},
{
"item": "wheat",
"totalAmt": 50
}
],
"totalSale": 250
},
{
"department": "cosmetics",
"saleDetails": [
{
"item": "cream",
"totalAmt": 100
},
{
"item": "powder",
"totalAmt": 200
}
],
"totalSale": 300
}
]
}
]
}
I have tried with the below query but not getting the proper response.
SELECT branch, department, ARRAY_AGG(STRUCT(item , totalAmt)) branchSaleList
FROM project.dataset.table
GROUP BY branch, department
ORDER BY branch, department limit 100
I need to do group by branch and then inside that group by department. Can someone help to build the query to get above response
Upvotes: 0
Views: 32