Olu Akin Olu Akin - 6 months ago 7
SQL Question

Querying primary keys

Please I have following results below, seven columns with a

primary key (reg_num)
:

reg_num day Morning Midday Lunch Evening Total_Period
0983776412 3 Yes Yes No Yes 3
0985674389 3 No Yes No No 1
0983464736 4 Yes No Yes No 2
0983466827 11 No No No No 0
0983481234 15 Yes Yes Yes Yes 4


Each row shows a combination of 'yes' and 'no' for a single day per
reg_num
. You will see the last column "
Total_Period
" is a count of all the '
yes
' in each row.

Please do you know how I can calculate the value for "
Total_Period
"?

Answer

the simplest way is:

select decode(Morning, 'Yes', 1, 0)  + 
       decode(Midday, 'Yes', 1, 0)   + 
       decode(Lunch, 'Yes', 1, 0)    + 
       decode(Evening , 'Yes', 1, 0) 
from your_table
Comments