none
SQL SERVER 2012 - Is it possible to check multiple columns in a row and count how many have a specific value and update a differnent column in the same table?

    Question

  • Hello everyone,

     Current Problem

                                                                                 [Need to write an update query to populate these values]

    Row #      Column A    Column B   Column C         (Met Count)      (Not Met Count)

    1                   Met           Met         Not Met                   2                      1

    2                Not Met     Not Met        Met                       1                      2

    The actual table has more than 3 fields to be counted.

    Any help / suggestions appreciated.


    • Edited by u828576 Friday, September 20, 2013 1:15 AM
    Friday, September 20, 2013 1:13 AM

Answers

  • declare @t table
    (col1 varchar(10),
    col2 varchar(10),
    col3 varchar(10),
    col4 varchar(10),
    Metcount int,
    NotMetcount int)
    
    insert into @t values ('Met', 'Met', 'NotMet','Met', NULL, NULL)
    insert into @t values ('NotMet', 'Met', 'NotMet','Met', NULL, NULL)
    insert into @t values ('Met', 'NotMet', 'NotMet','NotMet', NULL, NULL)
    
    update @t
    set Metcount = iif(col1='Met', 1,0)+iif(col2='Met', 1,0)+iif(col3='Met', 1,0)+iif(col4='Met', 1,0),
    	NotMetcount= iif(col1='NotMet', 1,0)+iif(col2='NotMet', 1,0)+iif(col3='NotMet', 1,0)+iif(col4='NotMet', 1,0)
    
    select * from @t

    You can also use computed column and need not write update statement.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by u828576 Friday, September 20, 2013 1:32 AM
    Friday, September 20, 2013 1:25 AM
    Moderator

All replies

  • declare @t table
    (col1 varchar(10),
    col2 varchar(10),
    col3 varchar(10),
    col4 varchar(10),
    Metcount int,
    NotMetcount int)
    
    insert into @t values ('Met', 'Met', 'NotMet','Met', NULL, NULL)
    insert into @t values ('NotMet', 'Met', 'NotMet','Met', NULL, NULL)
    insert into @t values ('Met', 'NotMet', 'NotMet','NotMet', NULL, NULL)
    
    update @t
    set Metcount = iif(col1='Met', 1,0)+iif(col2='Met', 1,0)+iif(col3='Met', 1,0)+iif(col4='Met', 1,0),
    	NotMetcount= iif(col1='NotMet', 1,0)+iif(col2='NotMet', 1,0)+iif(col3='NotMet', 1,0)+iif(col4='NotMet', 1,0)
    
    select * from @t

    You can also use computed column and need not write update statement.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by u828576 Friday, September 20, 2013 1:32 AM
    Friday, September 20, 2013 1:25 AM
    Moderator
  • Super - thank you!

    Friday, September 20, 2013 1:32 AM