'Hi i am working on a query which contains a group by clause. i will attach the table with data below. with previous and after scenarios

stopid routeid delid arrivaldate departuredate stopNo noofitems
112 22 1234 2022-10-04 2022-10-05 1.01 1000
232 22 2341 2022-10-05 2022-10-06 1.00 2000

i want a query to group by these 2 records in to one record like below

routeid arrivaldate departuredate noofitems stopNo
22 2022-10-04 2022-10-06 3000 1.00
mysqlsqldatabasegroup-by


Solution 1:[1]

You may aggregate by route and then take aggregates to obtain the desired column output:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY routeid ORDER BY departuredate DESC) rn
    FROM yourTable
)

SELECT
    routeid,
    MIN(arrivaldate) AS arrivaldate,
    MAX(departuredate) AS departuredate,
    SUM(noofitems) AS noofitems,
    MAX(CASE WHEN rn = 1 THEN stopNo END) AS stopNo
FROM cte
GROUP BY
    routeid;

Demo

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen

Related Questions