CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Milton
Milton

Reputation: 11

How to add an total line to a query

I have some queries that I need to add a total line at the end with the sums of columns with numeric values. I already tried the rollup statement but without success. Can someone help me?

I'm using an Oracle database.

Here's a simple example:

Create table funcionario_movimentos 
(
    id_funcionario number(7),
    cod_verba varchar2(4),
    valor number(13,2),
    mes_competencia number(2),
    ano_competencia number(4)
);

insert into funcionario_movimentos values (4358,'V003',8500.00,12,2022)
insert into funcionario_movimentos values (3521,'V003',11400.00,12,2022)
insert into funcionario_movimentos values (3242,'V003',18200.00,12,2022)

SELECT 
    fm.id_funcionario, fm.cod_verba, fm.valor
FROM 
    funcionario_movimentos fm
WHERE 
    mes_competencia = 12
    AND ano_competencia = 2022
    AND cod_verba = 'V003'
    AND id_funcionario IN (4358, 3521, 3242);

The result is as below:

Id_funcionario Cod_verba Valor
4358 V003 8500.00
3521 V003 11400.00
3242 V003 18200.00

But expected was:

Id_funcionario Cod_verba Valor
4358 V003 8500.00
3521 V003 11400.00
3242 V003 18200.00
38100.00

As we can see I need the total only in the columns with values.

Upvotes: 1

Views: 165

Answers (3)

cartbeforehorse
cartbeforehorse

Reputation: 3487

This is the simplest answer:

WITH funcionario_movimentos (id_funcionario, cod_verba, valor, mes_competencia, ano_competencia) AS (
   SELECT 4358, 'V003', 8500.00,  12, 2022 FROM dual UNION
   SELECT 3521, 'V003', 11400.00, 12, 2022 FROM dual UNION
   SELECT 3242, 'V003', 18200.00, 12, 2022 FROM dual
)
SELECT fm.id_funcionario, fm.cod_verba, sum(fm.valor) valor, count(1) nr
FROM   funcionario_movimentos fm
GROUP BY rollup((fm.id_funcionario, fm.cod_verba))

Note the double bracket (( xx )) around the rollup() in order that you group the "set" of columns together in the rollup. It's a very subtle detail ;-)

@d_r's answer is super clever, but way overkill unless you're into serious data-warehousing stuff.

enter image description here

Upvotes: 0

d r
d r

Reputation: 7826

You can use your query - just add MODEL clause:

SELECT  ID_FUNCIONARIO, COD_VERBA,  VALOR
FROM    FUNCIONARIO_MOVIMENTOS
WHERE   MES_COMPETENCIA = 12 AND 
        ANO_COMPETENCIA = 2022 AND 
        COD_VERBA = 'V003' AND 
        ID_FUNCIONARIO IN (4358,3521,3242)
MODEL
        Dimension By   (  ID_FUNCIONARIO  )
        Measures       (  Cast(COD_VERBA as VarChar2(12)) "COD_VERBA", VALOR )
        RULES
            ( VALOR[0] = Sum(VALOR)[ANY],
              COD_VERBA[0] = 'Total:'   )

R e s u l t:

ID_FUNCIONARIO COD_VERBA VALOR
4358 V003 8500
3521 V003 11400
3242 V003 18200
0 Total: 38100

More about MODEL here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142958

One option is to use partial rollup (see line #8), to reduce number of subtotals:

SQL> SELECT fm.id_funcionario, fm.cod_verba, SUM(fm.valor) valor
  2  FROM funcionario_movimentos fm
  3  WHERE mes_competencia = 12
  4    AND ano_competencia = 2022
  5    AND cod_verba = 'V003'
  6    AND id_funcionario IN (4358,3521,3242)
  7  --
  8  GROUP BY fm.cod_verba, ROLLUP (fm.id_funcionario);

ID_FUNCIONARIO COD_      VALOR
-------------- ---- ----------
          4358 V003       8500
          3521 V003      11400
          3242 V003      18200
               V003      38100

SQL>

Because, if you applied rollup to all non-aggregated columns, you'd get additional subtotals:

  <snip>
  8  GROUP BY ROLLUP (fm.id_funcionario, fm.cod_verba);

ID_FUNCIONARIO COD_      VALOR
-------------- ---- ----------
          4358 V003       8500
          3521 V003      11400
          3242 V003      18200
          4358            8500
          3521           11400
          3242           18200
                         38100

7 rows selected.

SQL>

Without any aggregates, you'd have to rollup all 3 columns and get result you really don't want:

SQL> SELECT fm.id_funcionario, fm.cod_verba, fm.valor
  <snip>
  8  GROUP BY ROLLUP (fm.id_funcionario, fm.cod_verba, fm.valor);

ID_FUNCIONARIO COD_      VALOR
-------------- ---- ----------
          4358 V003       8500
          3521 V003      11400
          3242 V003      18200
          4358 V003
          3521 V003
          3242 V003
          4358
          3521
          3242


10 rows selected.

SQL>

Just for amusement, if you use SQL*Plus, you can compute total using your "original" query as

SQL> break on report
SQL> compute sum of valor on report
SQL>
SQL> SELECT fm.id_funcionario, fm.cod_verba, fm.valor
  2  FROM funcionario_movimentos fm
  3  WHERE mes_competencia = 12
  4    AND ano_competencia = 2022
  5    AND cod_verba = 'V003'
  6    AND id_funcionario IN (4358,3521,3242);

ID_FUNCIONARIO COD_      VALOR
-------------- ---- ----------
          4358 V003       8500
          3521 V003      11400
          3242 V003      18200
                    ----------
sum                      38100

SQL>

Upvotes: 1

Related Questions