CODEWITHSUNDEEP
sqlsql-server
Acorian0
Acorian0

Reputation: 115

Sum result of SELECT...WHERE in SQL Server

Can someone tell me what I'm doing wrong, and if I can get the expect result... (Keep in mind this is a VIEW)

SELECT
      [Id]
    , [Nome]
    , [Estado]
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc = 'A1.' OR EstProc = 'A2.') AND LI.LogImpFiles_Id = LIF.Id) AS ItemsProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE EstProc = 'A3.' AND LI.LogImpFiles_Id = LIF.Id) AS ItemsErroProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc= 'A4' OR EstProc= 'A5') AND LI.LogImpFiles_Id= LIF.Id) AS ItemSucessoProcessamento
    , SUM(ItemsErroProcessamento + ItemSucessoProcessamento) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM
    [dbo].[LogImpFiles] AS LIF
GROUP BY 
    [Id], Nome, Estado, CreatedOn, CreatedBy

The result is this:

1   TesteImport1        6   2   3   0   2015-08-04 15:41:41.5130000 110032797

I was expecting something like this:

1   TesteImport1        6   2   3   **5**   2015-08-04 15:41:41.5130000 110032797

Upvotes: 0

Views: 146

Answers (3)

Ashish Kumar
Ashish Kumar

Reputation: 157

SELECT
    [Id]
    , [Nome]
    , [Estado]
    , ItemsProcessamento
    , ItemsErroProcessamento
    , ItemSucessoProcessamento
    , SUM(ItemsErroProcessamento + ItemSucessoProcessamento) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM
(
SELECT
      [Id]
    , [Nome]
    , [Estado]
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc = 'A1.' OR EstProc = 'A2.') AND LI.LogImpFiles_Id = LIF.Id) AS ItemsProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE EstProc = 'A3.' AND LI.LogImpFiles_Id = LIF.Id) AS ItemsErroProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc= 'A4' OR EstProc= 'A5') AND LI.LogImpFiles_Id= LIF.Id) AS ItemSucessoProcessamento
    , SUM(ItemsErroProcessamento + ItemSucessoProcessamento) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM
    [dbo].[LogImpFiles] AS LIF
GROUP BY 
    [Id], Nome, Estado, CreatedOn, CreatedBy
)BASE
GROUP BY 
    [Id], Nome, Estado, , ItemsProcessamento, ItemsErroProcessamento, ItemSucessoProcessamento,CreatedOn, CreatedBy

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use JOIN for your conditional aggregation instead of subquery. And as JamesZ pointed out, the SUM is not going to use the column alias.

SELECT
    [Id]
    , [Nome]
    , [Estado]
    , COUNT(CASE WHEN LI.EstProc = 'A1.' OR LI.EstProc = 'A2.' THEN LI.EstProc END) AS ItemsProcessamento
    , COUNT(CASE WHEN LI.EstProc = 'A3.' THEN LI.EstProc END) AS ItemsErroProcessamento
    , COUNT(CASE WHEN LI.EstProc= 'A4' OR LI.EstProc= 'A5' THEN LI.EstProc END) AS ItemSucessoProcessamento
    , SUM(CASE WHEN LI.EstProc IN('A1.', 'A2.', 'A4', 'A5') THEN 1 ELSE 0 END) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM [dbo].[LogImpFiles] AS LIF
LEFT JOIN LoginsImp LI
    ON .LogImpFiles_Id = LIF.Id
GROUP BY [Id], Nome, Estado, CreatedOn, CreatedBy

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I am tempted to say "it is just your data". That is, your question doesn't have enough information.

However, I suspect the problem is NULL values. The + returns NULL if either value is NULL. So, try this:

SUM(COALESCE(ItemsErroProcessamento, 0) + COALESCE(ItemSucessoProcessamento, 0)
   ) AS [ItemsProcessados]

Upvotes: 1

Related Questions