SQL Server Count for multiple conditions

Question

for the table below, how can I write a subquery for my select query so that I can return a flag column when the Inv = Esc for a given Num ?

In the example below, the second row is a match as well as the fifth row. Tried but keep getting boggled. Thanks.

**Additionally, there should be only one Flag for each Num.

    Num  |  Name   |Inv |Esc|Flag
1)  *785*   | AB7851     |155   |496  
2)  *785*   | AB7852     |**496 |496**  |**Hit**
3)  *785*   | AB7853     |518   |496  
4)  *785*   | AB7854     |769   |496  
5)  236 | AB785Q     |**155 |155**      |**Hit**
6)  236 | AB785R     |496   |155  
7)  236 | AB785S     |518   |155  
8)  236 | AB785T     |747   |155  
9)  236 | AB785U     |769   |155

select *, case when inv = esc then 1 else 0 end as flag from my_table; — The Impaler
– The Impaler, Commented Oct 24, 2018 at 20:50
Sorry, I don't want to add a column to the Table, but rather add a column to a select query pulling the data with a flag. — ORLANDO VAZQUEZ
– ORLANDO VAZQUEZ, Commented Oct 24, 2018 at 20:52
Also, it must be for each Num in the group since the Inv #s get used over and over. So for each Num, where Inv = Esc..... — ORLANDO VAZQUEZ
– ORLANDO VAZQUEZ, Commented Oct 24, 2018 at 20:53

D-Shih · Accepted Answer · 2018-10-24 21:19:51Z

1

You can try to use CASE WHEN

SELECT t1.*,
       case when Inv = Esc then 'HIT' end  as 'flag'
FROM T t1

sqlfiddle

If you want to update Flag column you can try this.

UPDATE T 
SET Flag =  (case when Inv = Esc then 'HIT' end )

edited Oct 24, 2018 at 21:19

answered Oct 24, 2018 at 20:57

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

6 Comments

The Impaler Over a year ago

from T tt -> from T t2

ORLANDO VAZQUEZ Over a year ago

Sorry, all the T tt t1 and t2 boggled me.

D-Shih Over a year ago

@ORLANDOVAZQUEZ Ok I saw you edit your question and I edit my answer

ORLANDO VAZQUEZ Over a year ago

Are you saying I should insert this as is as a subquery in the joins?

ORLANDO VAZQUEZ Over a year ago

How can I insert this into my existing query? For example, my query is currenlty: Select Num, Name, Inv, Esc From FROM P Join S on P.Num = S.Num

|

Gordon Linoff · Accepted Answer · 2018-10-24 21:52:08Z

1

I strongly advise you to do this using a computed column:

alter table t add flag as (case when inv = esc then 'hit' end);

A computed column is (generally) calculated when the table is queried. This ensures that the value is always accurate -- without having to create insert and update triggers.

answered Oct 24, 2018 at 21:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Server Count for multiple conditions

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related