skip to Main Content

I have following query. I need MODEL_YEAR_FW 2003 to 2022 to be grouped 3 years each

SELECT
COUNT(VEHICLE_FW.MODEL_YEAR_FW) AS COUNT_VEHICLES,
VEHICLES_FW.MODEL_YEAR_FW AS MODEL_YR
FROM
FWUSER.VEHICLES_FW
WHERE
VEHICLES_FW.ARCHIVE_STATUS_FW - 'N'
AND ( (MODEL_YEAR_FW) >2003)
GROUP BY
VEHICLE_FW.MODEL_YEAR_FW
ORDER BY
VEHICLES_FW.MODEL_YEAR_FW

Someone please help me

2

Answers


  1. You could use the modulo operator %

    SELECT VEHICLE_FW.MODEL_YEAR_FW % 3 AS interval3y, 
           count(*) AS COUNT_VEHICLES,
           min(VEHICLES_FW.MODEL_YEAR_FW) AS MODEL_YR
    FROM FWUSER.VEHICLES_FW
    WHERE VEHICLES_FW.ARCHIVE_STATUS_FW = 'N'
      AND MODEL_YEAR_FW >= 2003
    GROUP BY interval3y
    ORDER BY interval3y
    
    Login or Signup to reply.
  2. You may use DENSE_RANK() analytic function as the following:

    WITH RANKS AS
    (
      SELECT MODEL_YEAR_FW, ARCHIVE_STATUS_FW,
             CEILING(DENSE_RANK() OVER (ORDER BY MODEL_YEAR_FW)/3) GRP
      FROM VEHICLES_FW
      WHERE ARCHIVE_STATUS_FW = 'N'AND MODEL_YEAR_FW >2003
    )
    SELECT CONCAT(MIN(MODEL_YEAR_FW), '-', MAX(MODEL_YEAR_FW)) AS MODEL_YR,
           COUNT(MODEL_YEAR_FW) AS COUNT_VEHICLES
    FROM RANKS
    GROUP BY GRP
    ORDER BY GRP
    

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search