Mysql count column values and merge columns

Question

I was having problems in creating counting rows by grouping based on a given field value. For example: I have a Table A structure like this:

+------+------------+
| id   |  Person    |
+------+------------+
| 1    | "Sandy"    |
| 2    | "Piper"    |
| 3    | "Candy"    |
| 4    | "Pendy"    |
+------------+------+

Also I have a Table B structure like this:

+------+------------+---------+
| id   |  Person    |  Point  |
+------+------------+---------+
| 1    | "Sandy"    |  10     |
| 2    | "Piper"    |  20     |
| 3    | "Candy"    |  30     |
| 4    | "Sandy"    |  10     |
| 5    | "Piper"    |  20     |
| 6    | "Zafar"    |  30     |
+------------+------+---------+

And needed a result like:

+------+------------+---------+
| id   |  Person    |  Point  |
+------+------------+---------+
| 1    | "Piper"    |  40     |
| 2    | "Candy"    |  30     |
| 3    | "Zafar"    |  30     |
| 4    | "Sandy"    |  20     |
| 5    | "Pendy"    |   0     |
+------------+------+---------+

I hope the table examples are itself self-explanatory.

What is field id in result table? Is it the id of table 1 or is it an auto incremented value? — Jens
– Jens, Commented Aug 4, 2015 at 11:10
Actually the id shouldn't be related, the merge is based on the person name. And the resultant table should contain person from both the tables. I will make an edit more detailed. — mane
– mane, Commented Aug 4, 2015 at 11:14
I don't understand the 'id' column in the result set. What's that? — Strawberry
– Strawberry, Commented Aug 4, 2015 at 11:26

Strawberry · Accepted Answer · 2015-08-04 11:33:00Z

3

SELECT person
     , SUM(point) total 
  FROM 
     ( SELECT person,point FROM table_b
        UNION 
          ALL
       SELECT person,0 FROM table_a
     ) x
 GROUP 
    BY person 
 ORDER 
    BY total DESC;

answered Aug 4, 2015 at 11:33

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

Comments

Jens · Accepted Answer · 2015-08-04 11:25:48Z

2

It is a simple left join with a group by

select tableA.person, sum(tableB.points) from tableA left join tableB on tableA.person = tableB.person group by tableA.person
union
select tableB.person, sum(tableB.points) from tableB left join tableA on tableA.person = tableB.person  where tableA.id is null group by tableA.person

edited Aug 4, 2015 at 11:25

answered Aug 4, 2015 at 11:09

Jens

69.6k15 gold badges107 silver badges133 bronze badges

5 Comments

mane Over a year ago

Actually I need fields from both the tables if you see "Pendy" and "Zafar" are unique for table A and B respectively, won't using a left join cause a problem there??

Jens Over a year ago

@mane I have over seen it. It should be a text in your question. See my updated answer.

mane Over a year ago

I tried your answer and it seems to work for the particular problem. But the answer provided by Strawberry seems works too and is clean and less sql as well. But, comparing based on performance which would be better??

Jens Over a year ago

@mane I don't know. it depends on the ddl of your tables. How many records you expected in your table. Build test data and try wich one is better

mane Over a year ago

Ya, should do that. Thanks for helpin out.

Venkatesh Panabaka · Accepted Answer · 2015-08-04 11:08:23Z

0

I think below sql useful to you.

select a.id, a.Person,b.total_point  from (
select id, Person from tablea) as a join

(select Person, sum(Point) as total_point from tableb group by person) as b on a.person =b.person

Thank you

answered Aug 4, 2015 at 11:08

Venkatesh Panabaka

2,1644 gold badges21 silver badges27 bronze badges

1 Comment

mane Over a year ago

I tried your answer but I keep getting error, may be the unclosed ")"

Collectives™ on Stack Overflow

Mysql count column values and merge columns

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related