MySQL multiple count on the same column and insert the result to another table

Question

I have two tables as follows:

table1

id | fullname | gender | level
------------------------------
1  | Jerry    | Male   | Year 1
2  | Jen      | Female | Year 1
3  | Tom      | Male   | Year 2
4  | Natasha  | Female | Year 2

table2

level | male | female
----------------------
      |      |
      |      |

What I want to do is to INSERT the total no. of male and total no. of female from table1 into table2 group by the level.

The expected result supposed to look like the following table:

level  | male | female
---------------------
Year 1 |  1   |  1
Year 2 |  1   |  1

I've tried the following code:

INSERT INTO table2 (level, male, female)
SELECT level,
       SUM(gender = 'Male') male,
       SUM(gender = 'Female') female,
FROM table1
GROUP BY level

The above code I got it from one of the question asked here as it seemed to have the same problems as mine BUT when I run the code, it doesn't give me anything.

Is the problem from the tables itself? Or is it from the code?

THere is a comma after female. I consider this a typographic error and vote to close such questions. — Gordon Linoff
– Gordon Linoff, Commented Nov 3, 2014 at 18:35
@GordonLinoff I just realised about that. Thank you but it still doesn't work :( — Suji OK
– Suji OK, Commented Nov 3, 2014 at 18:40
It works. The order of the column were wrong. Sorry for the inconvenient. Thank you again :)) — Suji OK
– Suji OK, Commented Nov 3, 2014 at 18:58

Tushar Bhaware · Accepted Answer · 2014-11-03 19:09:43Z

1

This will solve your problem.

INSERT INTO table2 (level,male,female) 
SELECT level, 
SUM(CASE WHEN `gender` = 'Male' THEN 1 ELSE 0 END) AS male, 
SUM(CASE WHEN `gender` = 'female' THEN 1 ELSE 0 END) AS female 
FROM `table1` GROUP BY level

answered Nov 3, 2014 at 19:09

Tushar Bhaware

2,5251 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Suji OK Over a year ago

Hey. thanks for the response. But I have another problem. I want to update the whole table by replacing INSERT INTO to REPLACE INTO but instead of replacing the contains of the table, it keeps on adding them. I've tried a lot of possibility but it's just gave me the result.

Suji OK Over a year ago

*gace me the same result

Suji OK Over a year ago

There's another problem appear. I can update the table but it couldn't insert any new records :(

Tushar Bhaware Over a year ago

@SujiOK, Without looking at your statement and schema of your table, i can not help you much. But i should caution you as you are using REPLACE INTO. It is not always best solution. If handled inappropriately, it can do a lot of damage. Read comments here. Saying that, I think, You need to make column 'level' in table2 as primary.

Suji OK Over a year ago

I have solve that problem by just taking the value straight from table 1. I just didn't think of that way earlier. I'm so slow at that. Anyway, thank you from response. I appreciate it so much :))

Collectives™ on Stack Overflow

MySQL multiple count on the same column and insert the result to another table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related