Home Ask Login Register

Developers Planet

Your answer is one click away!

roger nm February 2016

how to insert values to a table with another table data using case statatement

enter image description here i have a table "test_comparitive_analysis" with column fields like gender, and value. Another table "cmparitive_analysis_dup" column fields like "boys" and "girls". The problem is that i want to insert all column values of "test_comparitive_analysis" to "cmparitive_analysis_dup". Condition is that if gender is "F" the "value" column data will move to "girls"column anf if it is "M" the "value" column data will move to "boys"

how can it possible

enter image description here

  "CREATE TABLE IF NOT EXISTS `comparitive_analysis_dup` (
        `cmb_id` int(5) NOT NULL AUTO_INCREMENT,
        `class_id` int(10) NOT NULL,
        `division` varchar(3) NOT NULL,
        `boys` varchar(100) NOT NULL,
        `girls` varchar(100) NOT NULL,
        `subject_name` varchar(20) NOT NULL,
        `exam_name` varchar(5) NOT NULL,
        `term_id` int(3) NOT NULL,
        PRIMARY KEY (`cmb_id`)


scaisEdge February 2016

Use two case (one for female, one for male) and a inner join

insert into cmparitive_analysis_dup(subject_name, boys, girls)
select a.subject_name,
   sum(case when a.gender = 'F' then a.value else 0 end),
   sum(case when b.gender = 'M' then b.value else 0 end)
from test_comparitive_analysis as a 
inner join test_comparitive_analysis as b on(a.cmb_id = b.cmb_id)
group by a.subject_name

Post Status

Asked in February 2016
Viewed 1,676 times
Voted 5
Answered 1 times


Leave an answer

Quote of the day: live life