Thevara Temple that have only one pathigam

only-one-thevara-pathigam-temples.sql

# List all temples that have only one pathigam order by thirumurai
sqlite> select koil_name_ta, cast(thirumurai_1 as integer) as t1,cast(thirumurai_2 as integer) as t2, cast(thirumurai_3 as integer) as t3,cast(thirumurai_4 as integer) as t4,cast(thirumurai_5 as integer) as t5,cast(thirumurai_6 as integer) as t6,cast(thirumurai_7 as integer)    as t7 from koil_pathigam WHERE ((thirumurai_1 is not null and COALESCE   (thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_2 is not null and COALESCE(thirumurai_1,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_3 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_4,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_4 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_5 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_6,thirumurai_7) is null) or (thirumurai_6 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_7) is null) or (thirumurai_7 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_6) is null) ) order by t7,t6,t5,t4,t3,t2,t1 asc;

# List count of temples that have only one pathigam order by thirumurai
sqlite> select koil_name_ta, count(cast(thirumurai_1 as integer)) as t1, count(cast(thirumurai_2 as integer)) as t2, count(cast(thirumurai_3 as integer)) as t3,count(cast(thirumurai_4 as integer)) as t4, count(cast(thirumurai_5 as integer)) as t5,count(cast(thirumurai_6 as integer)) as t6,count(cast(thirumurai_7 as integer)) as t7 from koil_pathigam WHERE ((thirumurai_1 is not null and COALESCE(thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_2 is not null and COALESCE(thirumurai_1,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_3 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_4,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_4 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_5,thirumurai_6,thirumurai_7) is null) or (thirumurai_5 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_6,thirumurai_7) is null) or (thirumurai_6 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_7) is null) or (thirumurai_7 is not null and COALESCE(thirumurai_1,thirumurai_2,thirumurai_3,thirumurai_4,thirumurai_5,thirumurai_6) is null) ) order by t7,t6,t5,t4,t3,t2,t1 asc;