CODEWITHSUNDEEP
sqldatabasepostgresqlsubquery
Xkid
Xkid

Reputation: 409

How could I calculate the percentage of total in SQL?

I'm using postgreSQL to create a query that be able to calculates the percentage of total based on a category (column "modelo_venta") and also has other different columns, I've made a try as follow but it's not working well and i'm not getting the 100% when I summarize the percentages:

SELECT
    pais,
    estatus,
    centro,
    drv,
    uen,
    agencia,
    modelo_venta,
    Sum(fuera_ruta_volumen_cartones) as fuera_ruta_volumen_cartones,
    Sum(fuera_ruta_volumen_hectolitros) as fuera_ruta_volumen_hectolitros,
    round(cast(count(modelo_venta) * 100.0 / (select count(*) FROM reporting_services.vw_mx_log_icaro_modelo_servicio_fuera_ruta) as numeric),3) as porcentaje,
    fecha_reparto
FROM
    reporting_services.vw_mx_log_icaro_modelo_servicio_fuera_ruta
WHERE
    modelo_venta IS NOT NULL
    and motivo_fuera_ruta = 'Otros Motivos'
group by
    pais,
    estatus,
    centro,
    drv
    uen,
    agencia,
    modelo_venta,
    fuera_ruta_volumen_cartones,
    fuera_ruta_volumen_hectolitros,
    fecha_reparto
ORDER BY
    porcentaje DESC

The output is somenthing like this:

modelo_ventas porcentaje
A .0002
B .10
C .10
D .003

The desired output would be something like:

modelo_ventas porcentaje
A 40
B 40
C 10
D 10

Do you know what is wrong here, guys? thanks by the way, best regards.

Upvotes: 1

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Try using window functions:

count(modelo_venta) * 100.0 / sum(count(modelo_venta)) over () as porcentaje,

The issue is that you want to count based on the results of the query, not the entire table.

Upvotes: 1

Related Questions