Create a new variable in SQL by groupby

student_R123 0
Viewed 35

I have 2 sql table as follows:

First table t1:

enter image description here

Second table t2:

enter image description here

I need to calculate the count of "Number" column based on "Name" column from t1 and merge it with t2.

I wrote following code. But it seems not working

select * 
from (
select Name, count(Number) as count
from t1 
 group by Name ) as a
join ( select *
from t2 ) as b
on a.Name = b.Name;

Can any one figure out what is wrong ? Thank you very much

mysql sql
1 Answers

I think you want to use SUM() instead of COUNT(). Because SUM() sums some integers, while COUNT() counts number of occurencies.

And as also stated in the comments, multiple columns with same names will create conflicts, so you have to select the wanted columns explicit (that is usually a good idea anyway).

You could obtain your wanted endgoal by this query:

select 
  SUM(Number),
  t1.Name,
  (select val1 FROM t2 WHERE t2.Name = t1.Name LIMIT 1) as val1
FROM t1 
GROUP BY t1.Name

Example in sqlfiddle: http://sqlfiddle.com/#!9/04dddf/7

thephper0
Related