CODEWITHSUNDEEP
sqlsql-serverselectcount
Lucas Müller
Lucas Müller

Reputation: 392

Counting results of rows in SQL Server

I need your help, i have this SQL :

SELECT 
    (SELECT 
         COUNT(b.IDPerfilPortal) 
     FROM 
         UseCRM.dbo.Produtos b 
     WHERE 
         a.IDProduto = b.ID AND b.IDPerfilPortal = 1) AS Completo, 
    (SELECT 
         COUNT(b.IDPerfilPortal) 
     FROM 
         UseCRM.dbo.Produtos b 
     WHERE 
         a.IDProduto = b.ID AND b.IDPerfilPortal >= 2) AS Segmentado
FROM 
    UseCRM.dbo.Contratos a
WHERE 
    (a.DataHoraCancelamento IS NULL) 
    AND (GETDATE() BETWEEN a.DataInicio AND a.DataTermino)

And I'm getting this :

Completo  Segmentado
--------------------
    1         0
    0         1
    1         0
    1         0
    1         0

I need to count the number of Products in Completo and Segmentado. Like this:

Completo  Segmentado
--------------------
  100         55

I need only one row. How can I do it?

Upvotes: 0

Views: 38

Answers (1)

James Z
James Z

Reputation: 12317

Instead of doing count twice for the different items, you can do sum with case like this:

SELECT 
    sum(case when b.IDPerfilPortal = 1 then 1 else 0 end) AS Completo, 
    sum(case when b.IDPerfilPortal >= 2 then 1 else 0 end) AS Segmentado
FROM
    UseCRM.dbo.Contratos a
    join UseCRM.dbo.Produtos b on a.IDProduto = b.ID
WHERE 
    (a.DataHoraCancelamento IS NULL) 
    AND (GETDATE() BETWEEN a.DataInicio AND a.DataTermino)

Upvotes: 3

Related Questions