Sql group records

Asked

Viewed 75 times

4

I have the following appointment:

select T1.dt_producao, T1.cod_turno, T1.cod_periodo,
    CASE
    WHEN T2.TP_PALLET = 'DZ' THEN
      sum(T1.QT_PROD* T2.QT_EMBALAGEM*12)
    ELSE
     sum(T1.QT_PROD* T2.QT_EMBALAGEM)
    END AS "APON.TOTAL",
    (select sum(T3.qt_entregue) from tb_romaneio_ovos T3
    where (T3.dt_romaneio = T1.dt_producao)
    and (T3.cod_turno >= T1.cod_turno) and (T3.cod_periodo = T1.cod_periodo)) as "QT.ENTREGUE",
    (select sum(T3.clas_totalovos) from tb_romaneio_ovos T3
   where (T3.dt_transf = T1.dt_producao)
   and (T3.cod_turno >= T1.cod_turno) and (T3.cod_periodo = T1.cod_periodo)) as "QT.CLASSIFICADO",
   (select sum(T3.clas_totalovostrin) from tb_romaneio_ovos T3
   where (T3.dt_transf = T1.dt_producao)
    and (T3.cod_turno >= T1.cod_turno) and (T3.cod_periodo = T1.cod_periodo)) as "QT.TRINCADOS"
    from tb_movimento T1, tb_cadastro T2
    where
    (T1.COD_TURNO >= 0)AND
    (T1.DT_PRODUCAO BETWEEN '26.09.2016' AND '05.10.2016') AND
    ((T1.COD_PERIODO >=0)) AND
    (T1.COD_SICOP = T2.COD_SICOP) AND
    (T1.COD_FILIAL = 'UDM')
    GROUP BY
    T1.DT_PRODUCAO, T1.cod_turno, T1.cod_periodo,t2.TP_PALLET

He returns to me like this:

|DT_PROD |COD_TURNO|COD_PERIODO|APONT.TOTAL|QT.ENTRE|QT.CLASSI|QT.TRINCADOS|
|01.10.16|   1     |     1     |   180600  | 248630 | 185182  | 7079       |
|03.10.16|   1     |     1     |   163260  | 520173 | 387798  | 15063      |
|03.10.16|   1     |     1     |   211140  | 520173 | 387798  | 15063      |
|04.10.16|   1     |     1     |   392160  | 519086 | 400622  | 18436      |

I would like to show only one day 03, adding the "TOTAL POINT", thus staying:

|DT_PROD |COD_TURNO|COD_PERIODO|APONT.TOTAL|QT.ENTRE|QT.CLASSI|QT.TRINCADOS|
|01.10.16|   1     |     1     |   180600  | 248630 | 185182  | 7079       |
|03.10.16|   1     |     1     |   374400  | 520173 | 387798  | 15063      |
|04.10.16|   1     |     1     |   392160  | 519086 | 400622  | 18436      |
sql database firebird

  • do not add <code> has the corresponding tag on Toolbar which are the keys { }

1 answer

4


Try using a subselect.

select dt_producao as DT_PROD ,cod_turno as COD_TURNO, cod_periodo as COD_PERIODO, sum(APONTTOTAL) as "APONT.TOTAL", 
QTENTREGUE as "QT.ENTREGUE"", QTCLASSIFICADO as "QT.CLASSIFICADO",
 QT.TRINCADOS as 'QTTRINCADOS' from 
(
    select T1.dt_producao, T1.cod_turno, T1.cod_periodo,
        CASE WHEN T2.TP_PALLET = 'DZ' THEN  sum(T1.QT_PROD* T2.QT_EMBALAGEM*12)
        ELSE sum(T1.QT_PROD* T2.QT_EMBALAGEM)
        END AS "APONTOTAL",
        (select sum(T3.qt_entregue) from tb_romaneio_ovos T3
        where (T3.dt_romaneio = T1.dt_producao)
        and (T3.cod_turno >= T1.cod_turno) and (T3.cod_periodo = T1.cod_periodo)) as "QTENTREGUE",

        (select sum(T3.clas_totalovos) from tb_romaneio_ovos T3
        where (T3.dt_transf = T1.dt_producao)
        and (T3.cod_turno >= T1.cod_turno) and (T3.cod_periodo = T1.cod_periodo)) as "QTCLASSIFICADO",

        (select sum(T3.clas_totalovostrin) from tb_romaneio_ovos T3
         where (T3.dt_transf = T1.dt_producao)
         and (T3.cod_turno >= T1.cod_turno) and (T3.cod_periodo = T1.cod_periodo)) as "QTTRINCADOS"
    from tb_movimento T1, tb_cadastro T2
    where
    (T1.COD_TURNO >= 0)AND
    (T1.DT_PRODUCAO BETWEEN '26.09.2016' AND '05.10.2016') AND
    ((T1.COD_PERIODO >=0)) AND
    (T1.COD_SICOP = T2.COD_SICOP) AND
    (T1.COD_FILIAL = 'UDM')
    GROUP BY
    T1.DT_PRODUCAO, T1.cod_turno, T1.cod_periodo,t2.TP_PALLET
)D
GROUP BY
    dt_producao ,COD_TURNO, COD_PERIODO, QTENTREGUE, QTCLASSIFICADO, QTTRINCADOS

Browser other questions tagged sql database firebird

You are not signed in. Login or sign up in order to post.