Previous Next JavaScript must be enabled to correctly display this content

## Aggregating results

Use the built in aggregate and sequence aggregate functions to find information such as a count, a sum, an average, a minimum, or a maximum.

Example 1: Find the total number of checked bags that are estimated to arrive at the LAX airport at a particular time.

    SELECT $estdate as ARRIVALDATE,
    count($flight) AS COUNT
    FROM BaggageInfo $bag,
    $bag.bagInfo.flightLegs.estimatedArrival $estdate,
    $bag.bagInfo.flightLegs.flightNo $flight,
    $bag.bagInfo.flightLegs.fltRouteDest $flt_dest
    WHERE $estdate =any "2019-02-01T11:00:00Z" AND $flt_dest =any "LAX"
    GROUP BY $estdate

Explanation: In an airline baggage tracking application, you can get the total count of checked bags that are estimated to arrive at a particular airport and time. For each flight leg, the `estimatedArrival` field in the `flightLegs` array of the `BaggageInfo` table contains the arrival time of the checked bags and the `fltRouteDest` field contains the destination airport code. In the above query, to determine the total number of checked bags arriving at the LAX airport at a given time, you first group the data with the estimated arrival time value using the GROUP BY clause. From the group, you select only the rows that have the destination airport as LAX. You then determine the bag count for the resultant rows using the count function.

Here, you can compare the string-formatted dates in ISO-8601 format due to the natural sorting order of strings without having to cast them into timestamp data types. The `$bag.bagInfo.flightLegs.estimatedArrival` and `$bag.bagInfo.flightLegs.fltRouteDest` are sequences. Since the comparison expression '=' cannot operate on sequences of more than one item, the sequence comparison operator '=any' is used instead to compare the `estimatedArrival` and `fltRouteDest` fields.

Output:

    {"ARRIVALDATE":"2019-02-01T11:00:00Z","COUNT":2}

Example 2: Display an automated message regarding the number of checked bags, travel route, and flight count to a passenger in the airline baggage tracking application.

    SELECT fullName,
    b.baginfo[0].routing,
    size(baginfo) AS BAGS,
        CASE
            WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 1
            THEN "You have one flight to catch"
            WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 2
            THEN "You have two flights to catch"
            WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 3
            THEN "You have three flights to catch"
            ELSE "You do not have any travel listed today"
        END AS FlightInfo
    FROM BaggageInfo b
    WHERE ticketNo = 1762320369957

Explanation: In the airline baggage tracking application, it is helpful to display a quick look-up message regarding the flight count, number of checked bags, and routing details of an upcoming travel for a passenger. The `bagInfo` array holds the checked bag details of the passenger. The size of the `bagInfo` array determines the number of checked bags per passenger. The `flightLegs` array in the `bagInfo` includes the flight details corresponding to each travel leg. The routing field includes the airport codes of all the travel fragments. You can determine the number of flights by counting the `flightNo` fields in the `flightLegs` array. If a passenger has more than one checked bag, there will be more than one element in the `bagInfo` array, one for each bag. In such cases, the `flightLegs` array in all the elements of the `bagInfo` field of a passenger data will contain the same values. This is because the destination of all the checked bags for a passenger will be the same. While counting the `flightNo` fields, you must consider only one element of the `bagInfo` array to avoid duplication of results. In this query, you consider only the first element, that is, `bagInfo[0]`. As the `flightLegs` array has a `flightNo` field for each travel fragment, it is a sequence and you determine the count of the `flightNo` fields per passenger using the `seq_count` function.

You use the CASE statement to introduce different messages based on the flight count. For ease of use, only three transits are considered in the query.

Output:

    {"fullName":"Lorenzo Phil","routing":"SFO/IST/ATH/JTR","BAGS":2,"FlightInfo":"You have three flights to catch"}