Summary functions in Query

Example of using summary functions in a query (MIN, GROUP)

We can use the summary functions in query as we use in the search

 const shipCostQuery = (lbs, stateZone, shipMethod) => {
            let results = []
            if (!stateZone && !shipMethod) return results

            let weight = lbs || 0

//CUSTOMLIST_VV_ZONE_CODE is a custom list
//CUSTOMRECORD_VV_SHIP_COST is a custom record
//custom record fields are accessed by using dots(.)

const sql = `SELECT CUSTOMLIST_VV_ZONE_CODE.id AS zone_id, ShipItem.id AS shipmethod_id, MIN(CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_weight) AS weight, MIN(CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_freight_charge) AS charge FROM CUSTOMRECORD_VV_SHIP_COST, ShipItem, CUSTOMLIST_VV_ZONE_CODE WHERE ((CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_ship_method = ShipItem.id AND CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_zone = CUSTOMLIST_VV_ZONE_CODE.id)) AND ((CUSTOMLIST_VV_ZONE_CODE.id IN ( ${stateZone} ) AND ShipItem.id IN ( ${shipMethod} ) AND CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_weight >= ${weight} AND (CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_weight >= ${weight} OR CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_weight IS NULL))) GROUP BY BUILTIN.CURRENCY(MIN(CUSTOMRECORD_VV_SHIP_COST.custrecord_vv_shipcost_freight_charge)), CUSTOMLIST_VV_ZONE_CODE.id, ShipItem.id ORDER BY CUSTOMLIST_VV_ZONE_CODE.id ASC NULLS LAST`

const sqlres = query.runSuiteQL({
                query: sql
            })

            _.each(sqlres.asMappedResults(), (result) => {
                results.push({
                    zone: result.zone_id,
                    shipmethod: result.shipmethod_id,
                    charge: result.charge
                })
                return true
            })
 return results
        }
//returned results value example

                {
                        "zone_id": 3,
                        "shipmethod_id": 263746,
                        "weight": 1,
                        "charge": 16.07
                },
                {
                        "zone_id": 3,
                        "shipmethod_id": 264162,
                        "weight": 1,
                        "charge": 31.74
                }
        ]