How to add an total line to a query-CodePudding

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

I'm using 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 columms with values.

CodePudding user response：

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>

CodePudding user response：

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