SQL query with SUM, displaying the return in rows [closed]

Question

SQL query with SUM, displaying the return in rows [closed]

Navigation

#1 by (1 votes)

1

So I have a question that is consuming my insides. Could someone please tell me how to return a total of records from a query, via SUM (SQL Server) function, displaying the total of rows proportional to the number of records rather than the value added that the function does?

Exemplifying by SUM: I get the total amount of money (just taking it as an example), and for that amount I display it in lines / records, suppose you have a total of $ 500, so you would have to display 500 lines (1,2,3,4 ... up to 500).

mysql sql sql-server

asked by anonymous 05.05.2017 / 23:52

1 answer

When I try to make a canvas, it does not do the error but also does nothing Problems with dynamic and struct allocation

score 1 · Accepted Answer

I will assume the existence of a table containing the numeric values:

CREATE TABLE #Tabela
(
    Codigo        INT,
    Montante      FLOAT,
)

INSERT INTO #Tabela(Codigo, Montante)VALUES
(1, 4),
(1, 2),
(2, 6),
(2, 1),
(3, 3),
(4, 2)

The sum of the sums can be obtained through the following query:

SELECT Codigo, SUM(Montante) Soma
FROM   #Tabela
GROUP BY Codigo

To generate the number of lines proportional to the value of the sum for each code, an alternative is as follows:

;WITH TodasAsSomas AS
(
    SELECT Codigo, SUM(Montante) Soma, ROW_NUMBER() OVER (ORDER BY SUM(Montante) DESC) RN
    FROM   #Tabela
    GROUP BY Codigo
),
Linhas AS
(
    SELECT 1 numL
    UNION ALL
    SELECT numL + 1
    FROM Linhas
    WHERE numL <= (SELECT Soma FROM TodasAsSomas WHERE RN = 1) 
)
SELECT S.Codigo, S.Soma, L.numL
FROM   TodasAsSomas S
INNER JOIN Linhas L
    ON L.numL <= S.Soma
ORDER BY 1, 2, 3
OPTION (maxrecursion 0)

Result:

Codigo  Soma    numL
1       6       1
1       6       2
1       6       3
1       6       4
1       6       5
1       6       6
2       7       1
2       7       2
2       7       3
2       7       4
2       7       5
2       7       6
2       7       7
3       3       1
3       3       2
3       3       3
4       2       1
4       2       2

Note that the sum can result in a decimal number; it may be necessary to round the result to get the desired number of lines;
If the goal is to generate the number of rows that corresponds to the sum of all values (without grouping by a code / key), you only need to remove the 'Code' column and the GROUP BY clause, for example:

;WITH SomaTotal AS
(
    SELECT SUM(Montante) Soma
    FROM   #Tabela
),
Linhas AS
(
    SELECT 1 numL
    UNION ALL
    SELECT numL + 1
    FROM Linhas
    WHERE numL <= (SELECT Soma FROM SomaTotal) 
)
SELECT S.Soma, L.numL
FROM   SomaTotal S
INNER JOIN Linhas L
    ON L.numL <= S.Soma
ORDER BY 1, 2
OPTION (maxrecursion 0)

Result:

Soma    numL
18      1
18      2
18      3
18      4
18      5
18      6
18      7
18      8
18      9
18      10
18      11
18      12
18      13
18      14
18      15
18      16
18      17
18      18