krishna
krishna

Reputation: 499

Group by multiple fields and return child element as list in BigQuery

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

Answers (0)

Related Questions