Get sum of field using multiple fields

SQL++
,
3

Hi @vsr1 ,

  • i just use TO_NUMBER function for totalStock
  • i need to sum across documents

Right now i am using this query:

SELECT  ARRAY_AGG(material.id) AS id, 
        ARRAY_AGG(material.materialCode) AS bpc, 
        ARRAY_AGG(material.size) AS size, 
        ARRAY_AGG(material.oldMaterialNumber) AS pattern,
        ARRAY_AGG(material.country) AS country,
        ARRAY_AGG(priceData.price) AS wsp,
        SUM(TO_NUMBER(stockData.totalStock)) AS totalStock
FROM bridgestone_test material
JOIN bridgestone_test stockData
ON KEYS material.stockId
JOIN bridgestone_test priceData
ON KEYS material.pricePrio3Id
WHERE material.docType = "MATERIAL"
GROUP BY material.size, material.oldMaterialNumber, material.country

I get this result:

{
      "bpc": [
        "LSR000223",
        "LSR008213"
      ],
      "country": [
        "JP",
        "JP"
      ],
      "id": [
        "MATERIAL::LSR000223::Z001",
        "MATERIAL::LSR008213::Z001"
      ],
      "pattern": [
        "R202Z",
        "R202Z"
      ],
      "size": [
        "195/85 R15     R202Z   T",
        "195/85 R15     R202Z   T"
      ],
      "totalStock": 9292,
      "wsp": [
        "155.00",
        "155.00"
      ]
    }

I was looking for another way to do it since i want the records to be separated by id, and bpc
thank you @vsr1