php - How to make a CASE statement dynamic in a MySQL Statement/Query -

php - How to make a CASE statement dynamic in a MySQL Statement/Query -

i have table containing age, gender, sid, , goalname. want grouping age/gender , display value of top 2 used goalnames each age/gender.

here's sample table:

i have managed output using query:

select age, gender, sum(case when sid = 'frank.net life cover' 1 else 0 end) 'frank.net life cover', sum(case when sid = 'frank.net salary protection' 1 else 0 end) 'frank.net salary protection', sum(case when sid = 'frank.net hospital cash back' 1 else 0 end) 'frank.net hospital cash back' (select uid, sid, goalname, gender, age sampletable age >=16 , age < 100 grouping age, gender);

here's output:

my desired output thing it's not dynamic. new goalnames added , won't applicable since manually set goalnames in case statement.... also, need display top 2 used goalnames each age/gender.

note: it's okay if couldn't display top 2 , display total of each goalnames instead. think can top 2 comparing totals of each goalname in php. need have dynamic sql query.

thank you!

the problem going land varying number of columns, messy process (and easier bring each different row anyway)

it possible using php dynamically build query, although wouldn't want in live situation:-

<?php $sql = "select distinct goalname sampletable"; if ($result = $mysqli->query($sql)) { $sum_col = array(); while ($row = $result->fetch_row()) { $sum_col[] = "sum(case when goalname= '".$mysqli->real_escape_string($row['goalname'])."' 1 else 0 end) '".$mysqli->real_escape_string($row['goalname'])."'" } if (count($sum_col) > 0) { $sql = "select age, gender, ".implode(",", $sum_col)." sampletable age >=16 , age < 100 grouping age, gender"; if ($result = $mysqli->query($sql)) { } } } ?>

however suggest easier simple grouped select , sort formatting out afterwards:-

select age, gender, goalname, count(*) sampletable age >=16 , age < 100 grouping age, gender, goalname

or if need every goal name every person, irrespective of whether selected or not:-

select age, gender, sub0.goalname, count(sampletable.sid) (select distinct goalname sampletable) sub0 left outer bring together sampletable on sub0.goalname = sampletable.goalname age >=16 , age < 100 grouping age, gender, sub0.goalname

getting top 2 bit more complicated, , logically impossible current output (as columns referring varying rows)

edit - getting highest 2 couple done this, brining both counts in 1 column (separated ## - alter goal name can never contain):-

select age, gender, substring_index(group_concat(concat_ws('=', goalname, goalcount) order goalcount desc separator '##'), '##', 2) ( select age, gender, goalname, count(sid) goalcount user age >=16 , age < 100 grouping age, gender, goalname ) sub1 grouping age, gender;

sql fiddle this:-

http://sqlfiddle.com/#!9/9cc8f/4

php mysql group-by case case-when