Jump to content
NelsonPaiva

Soma de vários Select

Recommended Posts

NelsonPaiva

Bom dia

Eu tenho o seguinte código que me apresenta os resultados numa tabela com as várias colunas.

SELECT ref, design, 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 34 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'LPP', 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 28 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'LPM', 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 42 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'RNPM', 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 68 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'RAM', 
       Isnull((SELECT Sum(qtt2) FROM bi(nolock) WHERE  bi.ndos = 68 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'RA' ,
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 59 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'GD' 
FROM   bi(nolock) temp 
WHERE  ( temp.ndos = 34 
          OR temp.ndos = 28 
          OR temp.ndos = 42 
          OR temp.ndos = 68 ) 
       AND temp.bofref like #1# 
       AND temp.ref NOT LIKE ' ' 
GROUP  BY ref, 
          design 
ORDER  BY ref 

Eu pretendo agora somar os valores de alguns dos select numa nova coluna, por exemplo pretendia somar as colunas "LPP, RNPM e RA" numa coluna total, no entanto não estou a conseguir colocar isso a funcionar.

Alguém me pode dar uma ajuda?

Share this post


Link to post
Share on other sites
Antonio Silva Magalhaes

Boa tarde,

8 horas atrás, NelsonPaiva disse:

SELECT ref, design, 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 34 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'LPP', 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 28 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'LPM', 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 42 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'RNPM', 
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 68 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'RAM', 
       Isnull((SELECT Sum(qtt2) FROM bi(nolock) WHERE  bi.ndos = 68 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'RA' ,
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 59 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'GD',
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 34 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) +
       Isnull((SELECT Sum(qtt) FROM bi(nolock) WHERE  bi.ndos = 42 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) +
       Isnull((SELECT Sum(qtt2) FROM bi(nolock) WHERE  bi.ndos = 68 AND bofref like #1# AND bi.ref = temp.ref GROUP  BY ref), 0) AS 'TOTAL'
FROM   bi(nolock) temp 
WHERE  ( temp.ndos = 34 
          OR temp.ndos = 28 
          OR temp.ndos = 42 
          OR temp.ndos = 68 ) 
       AND temp.bofref like #1# 
       AND temp.ref NOT LIKE ' ' 
GROUP  BY ref, 
          design 
ORDER  BY ref 

 

É grosseiro mas deveria resultar.

Abraço

Share this post


Link to post
Share on other sites
NelsonPaiva
Em 25/08/2017 às 19:58, Antonio Silva Magalhaes disse:

Boa tarde,

É grosseiro mas deveria resultar.

Abraço

Resulta de facto mas eu não estava a chegar lá. Andava a tentar de formas mais complexas e nem me lembrei do mais simples.

Obrigado.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.