Dev Faq
0
votes

Somme des valeurs de la même colonne dans SQL

J'ai besoin de montrer la somme d'une colonne, comme: résultat de la sélection

Comment puis-je créer une somme du "LQ", par exemple: 0 + 0 + 38 + 1010 + 216 + 664 CODE> P>

MON Code: P> 

select pla.DSC_ACO,
       sum(case when res.cod_ordem_producao like 'LQX%' then 1 else 0 end) as lqx,
       sum(case when res.cod_ordem_producao like 'LQP%' then 1 else 0 end) as lqp,
       sum(case when res.cod_ordem_producao like 'LQT%' then 1 else 0 end) as LQT,
       sum(case when res.cod_ordem_producao like 'LQRT%' then 1 else 0 end) as lqrt,
       sum(case when res.cod_ordem_producao like 'LQRZ%' then 1 else 0 end) as lqrz,
       sum(case when res.cod_ordem_producao like 'LQZ%' then 1 else 0 end) as LQZ,
       sum(case when res.cod_ordem_producao like 'LQRW%' then 1 else 0 end) as lqrw,
       sum(case when res.cod_ordem_producao like 'LQW%' then 1 else 0 end) as LQW,
       sum(case when res.cod_ordem_producao like 'LQ%' then 1 else 0 end) as Lq,
       sum(case when res.cod_ordem_producao like 'LQR%' then 1 else 0 end) as LQR
from QT_QTS.RES_TUBO_REVENIMENTO2 res, QT_QTS.PLA_ORDEM_PRODUCAO pla
where res.COD_ORDEM_PRODUCAO = pla.COD_ORDEM_PRODUCAO
and res.DTH_CRIACAO_REG >= :dthini 
and res.DTH_CRIACAO_REG <=:dthfim
group by pla.DSC_ACO

4 commentaires

fournir votre sortie attendue au format de tableau

Vous seriez double comptage LQ - cela semblerait déjà avoir les informations que vous voulez

Conseil d'aujourd'hui: utilisez toujours la syntaxe joindre . Plus facile à écrire (sans erreur), plus facile à lire et à entretenir, et plus facile à convertir en jointure extérieure si nécessaire!

Merci pour le conseil!

4 Réponses :

1
votes

J'espère que cela fonctionne :) 

select pla.DSC_ACO,
       sum(case when res.cod_ordem_producao like 'LQX%' then 1 else 0 end) as lqx,
       sum(case when res.cod_ordem_producao like 'LQP%' then 1 else 0 end) as lqp,
       sum(case when res.cod_ordem_producao like 'LQT%' then 1 else 0 end) as LQT,
       sum(case when res.cod_ordem_producao like 'LQRT%' then 1 else 0 end) as lqrt,
       sum(case when res.cod_ordem_producao like 'LQRZ%' then 1 else 0 end) as lqrz,
       sum(case when res.cod_ordem_producao like 'LQZ%' then 1 else 0 end) as LQZ,
       sum(case when res.cod_ordem_producao like 'LQRW%' then 1 else 0 end) as lqrw,
       sum(case when res.cod_ordem_producao like 'LQW%' then 1 else 0 end) as LQW,
       sum(case when res.cod_ordem_producao like 'LQ%' then 1 else 0 end) as Lq,
       sum(case when res.cod_ordem_producao like 'LQR%' then 1 else 0 end) as LQR
from QT_QTS.RES_TUBO_REVENIMENTO2 res, QT_QTS.PLA_ORDEM_PRODUCAO pla
where res.COD_ORDEM_PRODUCAO = pla.COD_ORDEM_PRODUCAO
and res.DTH_CRIACAO_REG >= :dthini 
and res.DTH_CRIACAO_REG <=:dthfim
group by pla.DSC_ACO
UNION ALL
SELECT 
'TOTAL' DSC,
SUM(lqx),
SUM(lqp),
SUM(LQT),
SUM(lqrt),
SUM(qrz),
SUM(LQZ),
SUM(lqrw),
SUM(LQW),
SUM(Lq),
SUM(LQR)
FROM (select pla.DSC_ACO,
       sum(case when res.cod_ordem_producao like 'LQX%' then 1 else 0 end) as lqx,
       sum(case when res.cod_ordem_producao like 'LQP%' then 1 else 0 end) as lqp,
       sum(case when res.cod_ordem_producao like 'LQT%' then 1 else 0 end) as LQT,
       sum(case when res.cod_ordem_producao like 'LQRT%' then 1 else 0 end) as lqrt,
       sum(case when res.cod_ordem_producao like 'LQRZ%' then 1 else 0 end) as lqrz,
       sum(case when res.cod_ordem_producao like 'LQZ%' then 1 else 0 end) as LQZ,
       sum(case when res.cod_ordem_producao like 'LQRW%' then 1 else 0 end) as lqrw,
       sum(case when res.cod_ordem_producao like 'LQW%' then 1 else 0 end) as LQW,
       sum(case when res.cod_ordem_producao like 'LQ%' then 1 else 0 end) as Lq,
       sum(case when res.cod_ordem_producao like 'LQR%' then 1 else 0 end) as LQR
from QT_QTS.RES_TUBO_REVENIMENTO2 res, QT_QTS.PLA_ORDEM_PRODUCAO pla
where res.COD_ORDEM_PRODUCAO = pla.COD_ORDEM_PRODUCAO
and res.DTH_CRIACAO_REG >= :dthini 
and res.DTH_CRIACAO_REG <=:dthfim
group by pla.DSC_ACO)

0 commentaires
0
votes

Vous pouvez utiliser une simple requête comme celle-ci pour obtenir le résultat pour lqr% prédicat que vous demandez dans votre question xxx

0 commentaires
0
votes 
select sum(lqx), sum(lqp), sum(lqt), sum(lqrt), sum(lqrz), sum(lqz), sum(lqrw), 
sum(lqw), sum(lq)
, sum(lqr) from
(select pla.DSC_ACO,
   sum(case when res.cod_ordem_producao like 'LQX%' then 1 else 0 end) as lqx,
   sum(case when res.cod_ordem_producao like 'LQP%' then 1 else 0 end) as lqp,
   sum(case when res.cod_ordem_producao like 'LQT%' then 1 else 0 end) as LQT,
   sum(case when res.cod_ordem_producao like 'LQRT%' then 1 else 0 end) as lqrt,
   sum(case when res.cod_ordem_producao like 'LQRZ%' then 1 else 0 end) as lqrz,
   sum(case when res.cod_ordem_producao like 'LQZ%' then 1 else 0 end) as LQZ,
   sum(case when res.cod_ordem_producao like 'LQRW%' then 1 else 0 end) as lqrw,
   sum(case when res.cod_ordem_producao like 'LQW%' then 1 else 0 end) as LQW,
   sum(case when res.cod_ordem_producao like 'LQ%' then 1 else 0 end) as Lq,
   sum(case when res.cod_ordem_producao like 'LQR%' then 1 else 0 end) as LQR
from QT_QTS.RES_TUBO_REVENIMENTO2 res, QT_QTS.PLA_ORDEM_PRODUCAO pla
where res.COD_ORDEM_PRODUCAO = pla.COD_ORDEM_PRODUCAO
and res.DTH_CRIACAO_REG >= :dthini 
and res.DTH_CRIACAO_REG <=:dthfim
group by pla.DSC_ACO) as values
0 commentaires
0
votes

Utilisez une expression de table commune pour organiser les données souhaitées, alors sélectionnez-en simplement avec une simple sommation. Le CTE dans la clause avec la clause vous permettra de choisir à partir de celui-ci comme si c'était une table normale. 

WITH temp_table AS (
    select pla.DSC_ACO,
           case when res.cod_ordem_producao like 'LQX%' then 1 else 0 end as LQX,
           case when res.cod_ordem_producao like 'LQP%' then 1 else 0 end as LQP,
           case when res.cod_ordem_producao like 'LQT%' then 1 else 0 end as LQT,
           case when res.cod_ordem_producao like 'LQRT%' then 1 else 0 end as LQRT,
           case when res.cod_ordem_producao like 'LQRZ%' then 1 else 0 end as LQRZ,
           case when res.cod_ordem_producao like 'LQZ%' then 1 else 0 end as LQZ,
           case when res.cod_ordem_producao like 'LQRW%' then 1 else 0 end as LQRW,
           case when res.cod_ordem_producao like 'LQW%' then 1 else 0 end as LQW,
           case when res.cod_ordem_producao like 'LQR%' then 1 else 0 end as LQR,
           case when res.cod_ordem_producao like 'LQ%' then 1 else 0 end as LQ
    from QT_QTS.RES_TUBO_REVENIMENTO2 res, QT_QTS.PLA_ORDEM_PRODUCAO pla
    where res.COD_ORDEM_PRODUCAO = pla.COD_ORDEM_PRODUCAO
        and res.DTH_CRIACAO_REG >= :dthini 
        and res.DTH_CRIACAO_REG <= :dthfim)
SELECT SUM(LQ) AS LQ_SUM FROM temp_table

1 commentaires

Merci pour l'aide!

Articles qui pourrait vous intéresser :

Ordre des requêtes de syntaxe SQL par
Problème de jointure sql, impossible de récupérer les enregistrements de la table a qui correspondent à une condition de la table b et les enregistrements restants de la table a
Interroger les données de PostgreSQL à partir de deux tables
Comment identifier les lignes par groupe avant un certain écart de valeur?