SQL - GROUP BY

This query works...
SELECT SUM(afn.amount)
FROM afn, prtrans
WHERE afn.prtrans_id = prtrans.prtrans_id
GROUP BY prtrans.prtrans_id;

This query runs but is not what I want...
UPDATE afn, po, pr, prtrans SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
+
IF ((afn.astatus = 'Submitted' OR (afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved')), afn.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;

This query fails but is what i want...
UPDATE afn, po, pr, prtrans SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
+
IF ((afn.astatus = 'Submitted' OR (afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved')), SUM(afn.amount), '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id
GROUP BY prtrans.prtrans_id;

Basically, I just trying to do a GROUP BY clause (SUM) and insert the data in a column.
myvpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
You can't update more than a single table once. You will have to do them separately.
0
myvpAuthor Commented:
I'm actually only updating one table (prtrans). I thought the code required the others since they are referanced. It works when I do the same thing but use amount and not SUM(amount)
0
TempDBACommented:
At this part you only give the name of the table which you need to update
UPDATE afn, po, pr, prtrans


so, suppose you have two tables and both have id field in common , and you want to update name of table1 with surname of table2 then
update t1
set t1.name = t2.surname
from table1 t1
inner join table2 t2
on t1.id = t2.id
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

myvpAuthor Commented:
I can find textbook examples myself, I need a real solution. What you suggest has nothing to do with a GROUP BY and SUM.
0
ralmadaCommented:
try
update 	prtrans, 
	(
	select afn.prtrans_id, sum(afn.amount) as samount
	from afn
	where afn.appr_date like '2011%' and afn.astatus = 'Approved'
	group by afn.prtrans_id
	) b, 
	po, pr
set a.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '') + b.samount
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND b.prtrans_id = prtrans.prtrans_id;

Open in new window

0
ralmadaCommented:
oops typo
update 	prtrans, 
	(
	select afn.prtrans_id, sum(afn.amount) as samount
	from afn
	where afn.appr_date like '2011%' and afn.astatus = 'Approved'
	group by afn.prtrans_id
	) b, 
	po, pr
set prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '') + b.samount
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND b.prtrans_id = prtrans.prtrans_id;

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
If this is MySQL, then you will want to have the JOIN in the UPDATE like so:

UPDATE a JOIN b ON a.id = b.id
SET ...

(or like you have it above using the UPDATE a, b SET ... WHERE a.id = b.id format)

To include your GROUP BY and SUM, simply derive that portion of the query.
i.e., alter the above as:
UPDATE a
JOIN (
   SELECT id, SUM(somevalue) AS SumOfValue
   FROM b
   GROUP BY id  
) b ON b.id = a.id
SET a.somevalue = b.SumOfValue
;

Hope that makes sense!
0
Kevin CrossChief Technology OfficerCommented:
It looks like ralmada posted while I was still typing and it appears he used your actual tables, so hopefully that helps you. My post was to help you understand how it works: http:#37345784 ; therefore, hopefully, you find it useful for future needs. Good luck!
0
myvpAuthor Commented:
Its starting to make sense and yese its MySQL 5.5

UPDATE       prtrans
JOIN      (
      SELECT afn.prtrans_id, SUM(afn.amount) AS samount
      FROM afn
      WHERE afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved'
      GROUP BY afn.prtrans_id
      )
      po, pr
SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;UPDATE       prtrans
JOIN      (
      SELECT afn.prtrans_id, SUM(afn.amount) AS samount
      FROM afn
      WHERE afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved'
      GROUP BY afn.prtrans_id
      )
      po, pr
SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;

now I get the error.....
Error Code: 1054
Unknown column 'po.pr_id' in 'where clause'

Yet there is a pr_id field in the po table?
0
myvpAuthor Commented:
Sorry it got pasted twice...

UPDATE       prtrans
JOIN      (
      SELECT afn.prtrans_id, SUM(afn.amount) AS samount
      FROM afn
      WHERE afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved'
      GROUP BY afn.prtrans_id
      )
      po, pr
SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;
0
ralmadaCommented:
you're missing a comma there and the alias check my comment http:#a37345774 again.

...
      group by afn.prtrans_id
      ) b,
      po, pr
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
myvpAuthor Commented:
Thanks, it works :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.