• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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.
0
myvp
Asked:
myvp
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
myvpAuthor Commented:
Thanks, it works :)
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now