I have the following columns in the DB:

Col1  Col2  Col3  Col4  Col5
AAA   NULL  AA1    AA1    0
NULL  BBB   AA1    AA1    0
CCC   NULL  BB1    BB2    1
DDD   NULL  BB1    BB2    1

Upon executing the 'needed' stored procedure, there will be a new column that will be created. The logic of the data of the new column will be like this:

1. Group the data based on col3, col4, col5
2. Depending on the group (group by col3, col4, col5), The data in the newly created column (Col6) will return data in col6 with this condition:

   1. If each row within the group has data in the col1 OR col2. Col6 =1 example: AA1 has 2 rows, it will check each row if col1 OR col2 has data
      if one of the columns IS NOT NULL, col6 = 1 Col3 Col4 Col5 Col6 AA1 AA1 0 1

   2. If each row within the group has only 1 data example each row has only data in col1 OR each row has only data in col2), Col6 = 2 example: BB1 has 2 rows, it will check each row if col1 or col2 has data. if both row has only data in col1 OR both rows has only data in col2 , col6 = 2 Col3 Col4 Col5 Col6 BB1 BB2 1 2

The expected result will be like this:

Col3  Col4  Col5  Col6
AA1    AA1    0    1 
BB1    BB2    1    2

Do you have any ideas to return the expected result? Thanks

CodePudding user response：

I'm not sure I completely understand your logic, but this may work:

select max(col3), max(col4), max(col5), if(max(col1) is null or max(col2) is null,2,1) col6
from Table1 group by col3, col4, col5

This will give a 2 in col6 if there is any non-null value in any col1 or col2. If, however, either col1 or col2 are completely null, it will give a 1. If both are completely null, it also gives 1.

see http://sqlfiddle.com/#!9/454c7a8/8

CodePudding user response：

@mankowitz has already provided the answer, but that supports MySql server. Here is the query which will be supported in MSSQL.

select max(col3) as col3, max(col4) as col4, max(col5) as col5, 
CASE WHEN MAX(COL1) IS NULL OR MAX(COL2) IS NULL THEN 2 ELSE 1 END AS Col6
from tablename group by col3, col4, col5

Don't upvote this answer, Credit goes to @mankowitz for answering.

CodePudding user response：

case when count(coalesce(col1, col2)) = count(*) /* all rows have a value */
    then case
        when count(col1) = count(*) and count(col2) = 0 then 2
        when count(col1) = 0 and count(col2) = count(*) then 2
        when count(col1)   count(col2) = count(*) then 1
        else null end /* some row has both values */
    else null end /* some row has no values */
end

You have more possible outcomes than what you've specified so it's not clear how those should be handled.