CODEWITHSUNDEEP
sqlsql-server
Camilo Ortiz
Camilo Ortiz

Reputation: 13

Get total in other row and total in column

I have the following need, I need to obtain the totals of both the columns and the rows, then I leave the code that I am using and also some images, thank you very much for the help.

    SELECT DDF.Codigo,
                    ISNULL((SELECT 
                              COUNT(Codigo)
                              FROM Detalle_
                              WHERE 1=1
                              AND Fecha_i >= @FECHA_INICIO
                              AND Fecha_i <= @FECHA_FIN
                              AND Estado = @ESTADO_N
                              AND  Codigo = DDF.Codigo
                              GROUP BY Codigo),0)Cantidad_No,

                    ISNULL((SELECT 
                              COUNT(Codigo)
                              FROM Detalle_
                              WHERE 1=1
                              AND Fecha_in >= @FECHA_INICIO
                              AND Fecha_in <= @FECHA_FIN
                              AND Estado = @ESTADO_C
                              AND  Codigo = DDF.Codigo
                              GROUP BY Codigo),0)Cantidad_C

        FROM Detalle_ DDF
        WHERE 1=1
                AND DDF.Fecha_i >= @FECHA_INICIO
                AND DDF.Fecha_i <= @FECHA_FIN
        GROUP BY Codigo

Now I have this

This is what I need

Thanks.

Upvotes: 0

Views: 50

Answers (2)

Charlieface
Charlieface

Reputation: 72435

Looks like you just need conditional aggregation

SELECT
    DDF.Codigo,
    COUNT(CASE WHEN Estado = @ESTADO_N THEN 1 END) Cantidad_No,
    COUNT(CASE WHEN Estado = @ESTADO_C THEN 1 END) Cantidad_C,
    COUNT(CASE WHEN Estado = @ESTADO_N THEN 1 END) + COUNT(CASE WHEN Estado = @ESTADO_C THEN 1 END) Total_

FROM Detalle_ DDF
WHERE DDF.Fecha_i >= @FECHA_INICIO
      AND DDF.Fecha_i <= @FECHA_FIN
 GROUP BY Codigo

If you would like to have a total across the whole rowset, use rollup:

....
GROUP BY ROLLUP (Codigo)

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 13009

For achieve right column with total, you have to sum them. For achieving bottom row with total, you have to UNION them. But, as mentioned in the comments, it is preferable to do them in the UI.

declare @TABLE table(codigo int, cantidad_no int, cantidad_c int) 

insert into @TABLE 
VALUES
(1,20,30), (1,20,50),(2,30,30), (2,30, 20);

select cast(codigo as varchar(20)) as codigo, sum(cantidad_no) as codigo_no, sum(cantidad_c) as codigo_c, cast(sum(cantidad_no)+ sum(cantidad_c) as varchar(20)) as total_  from @TABLE
group by codigo
UNION ALL
select 'Total' AS codigo, sum(cantidad_no), sum(cantidad_c), '' as total_  from @TABLE
codigo codigo_no codigo_c total_
1 40 80 120
2 60 50 110
Total 100 130

NB: Remember to cast the values to VARCHAR datatype, so that only you can have integer values & 'Total' in the same column.

Upvotes: 0

Related Questions