Percentage in SQL grouped by column

Question

Percentage in SQL grouped by column

Navigation

#1 by (1 votes)
#2 by (1 votes)

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

sql consulta porcentaje

asked by Hector Scesa 19.04.2018 в 17:06

source

2 answers

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

answered by 19.04.2018 в 17:49

How to change the background color of several buttons at once in windows forms? Error making the build from webpack

score 1 · Accepted Answer

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.