CODEWITHSUNDEEP
mysqlsqlaggregation
TDo
TDo

Reputation: 744

Update table based on aggregation from another table

I have a table t1 like this:

Names    Drug 

Tom      A
Tom      A
Tom      B
Lily     B
Lily     B

and a table t2 like this:

Names    Drug   COUNT(Drug)

Tom      A
Tom      B
Tom      C
Lily     A
Lily     B

I want to insert into the COUNT(drug) column of t2 the data from t1 after grouping by drug and name. The desired output of t2 looks like this:

Names    Drug   COUNT(Drug)

Tom      A      2
Tom      B      1
Tom      C      0
Lily     A      0
Lily     B      2

I am thinking about creating another table t3 with the GROUP BY drug, name data from t1 and then UPDATE t2 based on t3 but it is quite time-consuming.

Upvotes: 1

Views: 119

Answers (2)

flavi
flavi

Reputation: 73

I think this question can help you: specific mysql update based on group by data

You can do something like this:

UPDATE t2
LEFT JOIN
(
   SELECT name, drug, count(*) num
   FROM t1 
   GROUP BY name, drug
) r ON  t2.name = r.name AND t2.drug = r.drug 
SET t2.count_drag = COALESCE(r.num, 0);

Upvotes: 2

AdamMc331
AdamMc331

Reputation: 16691

Anytime you have a complicated problem like this, you should always try to break it up into smaller pieces that you can solve, and put them back together.

For example, I would start by getting the name/drug counts from the first table like this:

SELECT name, drug, COUNT(*) AS numDrugs
FROM t1
GROUP BY name, drug;

Then, you can use this as a subquery when updating t2 by joining the two tables on the condition that name and drug match. You should use a leftjoin, and coalesce function to replace the values that don't exist with 0:

UPDATE t2
LEFT JOIN(
  SELECT name, drug, COUNT(*) AS numDrugs
  FROM t1
  GROUP BY name, drug) t1 ON t1.name = t2.name AND t1.drug = t2.drug
SET drugCount = COALESCE(numDrugs, 0);

Here is an SQL Fiddle example.

Upvotes: 2

Related Questions