MySQL Update/Group By Querry

This query gives me a correct total amount........

UPDATE project j,
      (
      SELECT b.project_id, SUM(b.amount) AS samount
      FROM budget b
      GROUP BY b.project_id
      ) z, budget b
SET j.B_AMT = z.samount
WHERE z.project_id = j.project_id;

This is the same basic query but should filter out some data based on the "AND c.ctype = 'CapEx';" part of the query but it gives me the some total as above.

UPDATE project j,
      (
      SELECT b.project_id, SUM(b.amount) AS samount
      FROM budget b
      GROUP BY b.project_id
      ) z, budget b, capital c
SET j.BCAP_AMT = z.samount
WHERE z.project_id = j.project_id
AND b.capital_id = c.capital_id
AND c.ctype = 'CapEx';
myvpAsked:
Who is Participating?
 
Pratima PharandeCommented:
try this

UPDATE project j,
      (
      SELECT b.project_id, SUM(b.amount) AS samount
      FROM budget b, capital c
where  b.capital_id = c.capital_id and AND c.ctype = 'CapEx
      GROUP BY b.project_id
      ) z
SET j.BCAP_AMT = z.samount
WHERE z.project_id = j.project_id;
0
 
Pratima PharandeCommented:
Yes it is correct
  SELECT b.project_id, SUM(b.amount) AS samount
      FROM budget b
      GROUP BY b.project_id

this query returning you the sum of amount first
depending on the project_id
and you are filtering on c.ctype = 'CapEx'
If there is no match for this filter the project id itself get dropped the sum will not change
0
 
gnoonCommented:
I think ctype filter is not applied to SUM() in the 2nd query. It should be this


UPDATE project j,
      (
      SELECT b.project_id, SUM(b.amount) AS samount
      FROM budget b, capital c
      WHERE b.capital_id = c.capital_id
      AND c.ctype = 'CapEx';
      GROUP BY b.project_id
      ) z
SET j.BCAP_AMT = z.samount
WHERE z.project_id = j.project_id;
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pratima PharandeCommented:
gnoon , same query I have suggested before :)
0
 
myvpAuthor Commented:
Got it. Thanks  other than you had two ands together.
0
 
gnoonCommented:
sorry I miss refresh.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.