Sum result of SELECT...WHERE in SQL Server

Question

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

You have the same name in the alias and in the table too? The sum is not going to use the column alias but the column from the table. — James Z
– James Z, Commented Aug 4, 2015 at 15:54

Felix Pamittan · Accepted Answer · 2015-08-04 15:58:17Z

1

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

answered Aug 4, 2015 at 15:58

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-08-04 15:52:30Z

1

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]

answered Aug 4, 2015 at 15:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Acorian0 Over a year ago

I should have explained, the SUM is the result of columns 5 and 6 (so, 2 + 3), meaning, no NULL's ;)

Siyual · Accepted Answer · 2015-08-04 17:39:42Z

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

Collectives™ on Stack Overflow

Sum result of SELECT...WHERE in SQL Server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related