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`)
        ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; " 

Answers


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

Search




Leave an answer