Percentage in SQL grouped by column

1

I have the following table structure:

From which you want to take the percentage of the value that comes in the loans column grouped by number. of cuit.

I have tried this but in all cases 100% comes to me

``````    SELECT
cdd.NumeroIdentificacion ,
cdd.Prestamos ,
CAST(REPLACE(cdd.Prestamos,',','.') AS FLOAT) * 100 /( SELECT
SUM(CAST(REPLACE(cdd.Prestamos,',','.') AS FLOAT))
FROM
CentralDeDeudores AS a
WHERE
a.NumeroIdentificacion = cdd.NumeroIdentificacion
GROUP BY
a.NumeroIdentificacion
) AS porcentaje

FROM
CentralDeDeudores cdd

GROUP BY
cdd.NumeroIdentificacion,cdd.Prestamos
``````

asked by Hector Scesa 19.04.2018 в 17:06
source

1

Good day considering your concern you can look at the following example:

``````SELECT  T1.NumeroIdentificacion ,
T1.Prestamos,
(SELECT COUNT(c.NumeroIdentificacion)
FROM CentralDeDeudores c
WHERE c.NumeroIdentificacion = T1.NumeroIdentificacion
GROUP BY c.NumeroIdentificacion ) AS Cantidad,
ROUND((CAST(REPLACE(T1.Prestamos,',','.') AS FLOAT)/T2.TotalPrestamos),3) * 100 AS Porcentaje
FROM CentralDeDeudores T1
INNER JOIN (SELECT  NumeroIdentificacion,
SUM(CAST(REPLACE(Prestamos,',','.') AS FLOAT))  AS 'TotalPrestamos'
FROM CentralDeDeudores
GROUP BY
NumeroIdentificacion
) T2
ON T2.NumeroIdentificacion = T1.NumeroIdentificacion
``````

It is important to bear in mind that 100% is for each amount if we say there are 4 elements the percentage will be divided into those 4 items that have an identical identifier and so for each quantity that you have in your records, I share an img . how it would look:

I hope it will be useful for you. Greetings.

source
1

The basic form in `SQL` would be to obtain in a query the totals of `Prestamos` by `NumeroIdentificacion` and then integrate it into another query with each one of the loans to reach the percentage of them. Something like this:

``````SELECT  T1.NumeroIdentificacion ,
T1.Prestamos/T2.TotalPrestamos
FROM CentralDeDeudores T1
INNER JOIN (    SELECT  NumeroIdentificacion ,
SUM(Prestamos)  AS 'TotalPrestamos'
FROM CentralDeDeudores
GROUP BY
NumeroIdentificacion
) T2
ON T2.NumeroIdentificacion = T1.NumeroIdentificacion
``````