resultat of the code

I want to sum the lengths when PROPRIETAI = 'PRIVE' in the same line with sql and this is my code :

SELECT
  PROPRIETAI,TYPE_STRUC,
  sum(ROUND((LGR_REEL))) as "Longueur (m)"
FROM SUPPORT
GROUP BY PROPRIETAI, TYPE_STRUC
ORDER BY CASE WHEN PROPRIETAI = 'FT' THEN 1 
              WHEN PROPRIETAI = 'FREE MOBILE' THEN 2 
              WHEN PROPRIETAI = 'PRIVE' THEN 3 
              ELSE 4
           END

and I want this table resultat

I try to make some changes but it's not working

CodePudding user response：

Replace all values of type_struc with NULL when proprietai = 'PRIVE'

That will ensure you only ever get one 'PRIVE' output row, and so all the lengths will be aggregated.

SELECT
  s.proprietai,
  CASE WHEN s.proprietai = 'PRIVE' THEN NULL ELSE s.type_struc END  AS type_struc,
  SUM(ROUND((s.lgr_reel))) as "Longueur (m)"
FROM
  support   AS s
GROUP BY
  s.proprietai,
  CASE WHEN s.proprietai = 'PRIVE' THEN NULL ELSE s.type_struc END
ORDER BY
  CASE s.proprietai
    WHEN 'FT'          THEN 1
    WHEN 'FREE MOBILE' THEN 2
    WHEN 'PRIVE'       THEN 3 
                       ELSE 4
  END