sql - Aggregate functions to specific values in a Group By query -

sql - Aggregate functions to specific values in a Group By query -

i have info (for example):

id col1 col2 -------------------------------- 5614 (null) y 5614 y (null) 5614 y (null)

i want obtain single line tells me if fields has value y.

id col1 col2 5614 y y

if no value y present, should n.

my actual query that, think should shorter way:

select op.id, case when (sum(case when col1 = 's' 1 else 0 end)) > 0 's' else 'n' end col1, case when (sum(case when col2 = 's' 1 else 0 end)) > 0 's' else 'n' end col2 operator op left bring together tram_op tr on op.id = tr.id , tr.id2 = 20 op.id = 5614 grouping op.id

as can see have 2 tables , need bring together them match id2 key.

one way utilize shorthand eliminates need of case statements:

select op.id, (case when sum(col1 = 's') > 0 's' else 'n' end) col1, (case when sum(col2 = 's') > 0 's' else 'n' end) col2 operator op left bring together tram_op tr on op.id = tr.id , tr.id2 = 20 op.id = 5614 grouping op.id;

you take advantage of fact 's' > 'n':

select op.id, max(col1) col1, max(col2) col2 operator op left bring together tram_op tr on op.id = tr.id , tr.id2 = 20 op.id = 5614 grouping op.id;

however, version might confusing else reading code.

sql sqlite group-by