CODEWITHSUNDEEP
sqlsql-servert-sql
90abyss
90abyss

Reputation: 7337

SQL Server: how to include count in the select query with where clause?

Let's say I have this table:

id     out    flag
---   ---     ---
 1     0       1
 1     1       0
 1     4       0
 2     0       1
 2     2       0
 2     2       0
 3     0       1
 3     2       0
 3     1       0
 3     4       0

I want to count the number of rows with out=(0, 2, or 4) and later display rows where flag = 1

Basically: select id, count(where out IN(0,2,4)) as cnt where flag = 1

My desired output: 

 id  cnt
---  ----
 1     2
 2     3
 3     3

This query works as expected but only when I don't have "where flag = 1":

select id,sum(case when out in (0,2,4) then 1 else 0 end) over(partition by id) as cnt
from tablename

Is there a way for me to calculate sum() of something first and store it in a column, and later filter out rows using where clause? currently what's happening is that rows are being filtered out first and later sum(...) is calculated.

Any way I can rectify this?

Upvotes: 1

Views: 3180

Answers (3)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Do count for 0,2,4 OUT. And then filter them with a Inner Join or using Correlated subquery

Schema:

CREATE TABLE #TAB (ID  INT,   OUT_VALUE INT,    FLAG INT)

INSERT INTO #TAB
SELECT 1,  0, 1
UNION ALL
SELECT  1,  1, 0
UNION ALL
SELECT  1,  4, 0
UNION ALL
SELECT  2,  0, 1
UNION ALL
SELECT  2,  2, 0
UNION ALL
SELECT  2,  2, 0
UNION ALL
SELECT  3,  0, 1
UNION ALL
SELECT  3,  2, 0
UNION ALL
SELECT  3,  1, 0
UNION ALL
SELECT  3,  4, 0
UNION ALL
SELECT  4,  4, 0

Now do select like below

INNER JOIN

SELECT T1.ID,COUNT(1) C
FROM #TAB T1
INNER JOIN 
(
        SELECT DISTINCT ID FROM #TAB WHERE FLAG = 1
)FLAGS ON T1.ID = FLAGS.ID
 WHERE T1.OUT_VALUE IN (0,2,4)
GROUP BY T1.ID

Correlated Sub-query

SELECT ID, C  FROM (
SELECT T1.ID
,COUNT(1) C
,(
        SELECT DISTINCT ID
        FROM #TAB T2
        WHERE T1.ID = T2.ID
            AND T2.FLAG = 1
        ) FLAG


FROM #TAB T1 WHERE T1.OUT_VALUE IN (0,2,4)
GROUP BY T1.ID

)A
WHERE A.FLAG IS NOT NULL

The result will be

+----+---+
| ID | C |
+----+---+
|  1 | 2 |
|  2 | 3 |
|  3 | 3 |
+----+---+

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a case expression to sum only rows for an id when they have specified values for the out column.

select id,sum(case when out in (0,2,4) then 1 else 0 end) as cnt
from tablename
group by id

Edit: To include other columns when summing, use the sum window function.

select * from (select id,flag
               ,sum(case when out in (0,2,4) then 1 else 0 end) over(partition by id) as cnt
               --include other columns as required
               from tablename
              ) x 
where flag=1

Upvotes: 2

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

select      id,count(*) as cnt
from        tablename
where       out in (0,2,4)
group by    id

Upvotes: 0

Related Questions