return value when count equals variable in sql server

Question

I have a table to update with other table data. For this I created a trigger. Inside trigger, I must check how many active occurrences of each id. If this number of occurrences is same number than a variable value then return 1 (true) otherwise 0 (false).

I get the variable

DECLARE @num_gerencias numeric(2, 0)
SELECT @num_gerencias = (SELECT COUNT(id_gerencia) FROM FR_GERENCIES)
select @num_gerencias

This works ok... returns 5

Then, I make a count of occurrences of the l_activo variable in other table (variable is a bit):

SELECT id_operacio, SUM(CASE WHEN l_activo = 1 THEN 1 ELSE 0 END)
                FROM FR_GERENCIES_OPERACIONS o 
                GROUP BY o.id_operacio

This query also works nice, returns:

All perfect... now i must get same list, but if sum equals to @num_gerencias, then put 1 and 0 otherwise.

Expected result table

I've tried with CASE

SELECT DISTINCT id_operacio, CASE WHEN 
            (
                SELECT SUM(CASE WHEN l_activo = 1 THEN 1 ELSE 0 END)
                FROM FR_GERENCIES_OPERACIONS o 
                GROUP BY o.id_operacio
            ) = @num_gerencias THEN 1 ELSE 0 END

but I'm getting error:

Mens . 512 , Level 16 , State 1, Line 6 The subquery returned more than one value , which is not correct when it goes below = , ! = , <, < = ,>, > = Or when used as an expression .

I also tried with an IF (i guess this option is totally wrong for this case... but I've tried)

SELECT DISTINCT id_operacio,
    IF @num_gerencias = (SELECT SUM(CASE WHEN l_activo = 1 THEN 1 ELSE 0 END)
            FROM FR_GERENCIES_OPERACIONS o 
            GROUP BY o.id_operacio) 
    1
    ELSE 0
FROM FR_GERENCIES_OPERACIONS

But I have syntax errors...

Any idea how can i reach expected result table?

Wibbler · Accepted Answer · 2015-07-09 12:07:57Z

2

You were nearly there, however your grouping and selection must occur outside of your case statement:

SELECT DISTINCT 
    id_operacio
    ,CASE 
        WHEN SUM(CAST(l_activo AS INTEGER)) = @num_gerencias THEN 1 
        ELSE 0
    END
FROM FR_GERENCIES_OPERACIONS o 
GROUP BY o.id_operacio

edited Jul 9, 2015 at 12:07

answered Jul 9, 2015 at 12:03

Wibbler

1,0439 silver badges17 bronze badges

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

3 Comments

Jordi Castilla Over a year ago

it complains that bit cannot be used in sum

Wibbler Over a year ago

Cast the bit to an integer with CAST(l_activo AS INTEGER)

Jordi Castilla Over a year ago

sweet answer.... really smart to use the bit value instead inner CASE thanks for your help @Wibbler :)

Collectives™ on Stack Overflow

return value when count equals variable in sql server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related