How to get Sum of many columns as one

am working to get percentage of GSM , DEPOT , IPMSAN ..Etc and I would like to sum the counts within each column that correspond to each

WITH q2 AS ( SELECT c2.compteur, c1.police ,
       ( SUM(c1.jan2016 + c1.feb2016 + c1.mar2016) * 100 / 
         SUM(SUM(c1.jan2016 + c1.feb2016 + c1.mar2016)) OVER ()
       )  as Perc16 FROM compteur2016 c1 JOIN
     compteur c2 
     ON c1.compteur = c2.compteur GROUP BY c2.compteur, c1.police ORDER BY Perc16 desc) , q1 AS  (SELECT c2.naturebat, c1.police ,
       ( SUM(c1.feb15 + c1.jan15 + c1.mar15) * 100 / 
         SUM(SUM(c1.feb15 + c1.jan15 + c1.mar15)) OVER ()
       )  as Perc15 FROM compteur2015 c1 JOIN
     compteur c2 
     ON c1.compteur = c2.compteur GROUP BY c2.naturebat, c1.police ORDER BY Perc15 desc) select q1.naturebat ,  q1.Perc15 , q2.Perc16  FROM q1 JOIN q2  join compteur  ON q1.police = q2.police and compteur.police = q2.police where Perc15 is not null  and Perc16 is not null  group by q1.naturebat, q1.Perc15 , q2.Perc16  ORDER BY q2.Perc16 desc

​x
 
WITH q2 AS ( SELECT c2.compteur, c1.police ,       ( SUM(c1.jan2016 + c1.feb2016 + c1.mar2016) * 100 /          SUM(SUM(c1.jan2016 + c1.feb2016 + c1.mar2016)) OVER ()       )  as Perc16 FROM compteur2016 c1 JOIN     compteur c2      ON c1.compteur = c2.compteur GROUP BY c2.compteur, c1.police ORDER BY Perc16 desc) , q1 AS  (SELECT c2.naturebat, c1.police ,       ( SUM(c1.feb15 + c1.jan15 + c1.mar15) * 100 /          SUM(SUM(c1.feb15 + c1.jan15 + c1.mar15)) OVER ()       )  as Perc15 FROM compteur2015 c1 JOIN     compteur c2      ON c1.compteur = c2.compteur GROUP BY c2.naturebat, c1.police ORDER BY Perc15 desc) select q1.naturebat ,  q1.Perc15 , q2.Perc16  FROM q1 JOIN q2  join compteur  ON q1.police = q2.police and compteur.police = q2.police where Perc15 is not null  and Perc16 is not null  group by q1.naturebat, q1.Perc15 , q2.Perc16  ORDER BY q2.Perc16 desc​

this is the actual result :

naturebat   perc15  perc16
GSM 1.138445261 7.9194700502
DEPOT   0.2139114866    5.02366065
CENTRAL 0.1749475287    3.7602955112
GSM 0.4925794953    3.2469454867
CENTRAL 0.6270803657    3.1276458277
CENTRAL 1.0978046969    2.3177302785
GSM 1.0811312649    2.1836340303
GSM 1.3243077123    1.7440525492
GSM 1.3697459021    1.6935745684
CENTRAL 0.6196699515    1.6862378058
GSM 1.0996573005    1.5648312198
CENTRAL 0.1434532683    1.445898706
GSM 1.5603738673    1.4334178177
COMMERCIALE 1.7122966215    1.4151447349
GSM 0.1990906582    1.2812710765
IPMSAN  0.5979734938    1.2495047058
GSM 0.4243499591    1.1800590088
CENTRAL 5.2499178247    1.1620225875
GSM 1.0780435924    1.1589626182
GSM 0.6086815423    1.156915816
GSM 1.1052151111    1.1563833915
GSM 2.012420867 1.1558924149

 
naturebat   perc15  perc16GSM 1.138445261 7.9194700502DEPOT   0.2139114866    5.02366065CENTRAL 0.1749475287    3.7602955112GSM 0.4925794953    3.2469454867CENTRAL 0.6270803657    3.1276458277CENTRAL 1.0978046969    2.3177302785GSM 1.0811312649    2.1836340303GSM 1.3243077123    1.7440525492GSM 1.3697459021    1.6935745684CENTRAL 0.6196699515    1.6862378058GSM 1.0996573005    1.5648312198CENTRAL 0.1434532683    1.445898706GSM 1.5603738673    1.4334178177COMMERCIALE 1.7122966215    1.4151447349GSM 0.1990906582    1.2812710765IPMSAN  0.5979734938    1.2495047058GSM 0.4243499591    1.1800590088CENTRAL 5.2499178247    1.1620225875GSM 1.0780435924    1.1589626182GSM 0.6086815423    1.156915816GSM 1.1052151111    1.1563833915GSM 2.012420867 1.1558924149​

the result i want is like this :

naturebat   perc15  perc16
GSM             percentage of all GSM in 2015  percentage of all GSM in 2016
..
..
..

 
naturebat   perc15  perc16GSM             percentage of all GSM in 2015  percentage of all GSM in 2016......​

Answer

Use below query to select in your CTE:

SELECT q1.naturebat,
           SUM(q1.Perc15),
           SUM(q2.Perc16)
    FROM q1
      JOIN q2
      JOIN compteur
        ON q1.police = q2.police
       AND compteur.police = q2.police
    WHERE Perc15 IS NOT NULL
    AND   Perc16 IS NOT NULL
    GROUP BY q1.naturebat,
    ORDER BY q2.Perc16 DESC

 
SELECT q1.naturebat,           SUM(q1.Perc15),           SUM(q2.Perc16)    FROM q1      JOIN q2      JOIN compteur        ON q1.police = q2.police       AND compteur.police = q2.police    WHERE Perc15 IS NOT NULL    AND   Perc16 IS NOT NULL    GROUP BY q1.naturebat,    ORDER BY q2.Perc16 DESC​

Advertisement

Answer