RE: SQL SUM in complex stmt

I'd probably do a common table expression, which is kind of like a virtual temp table:

WITH sourcedta AS (
select * from miscchg A
where exists
(select * from miscchgplg b
where
a.ASHPNBR = b.ashpnbr
and a.ABLDNGD = b.ABLDNGD )
union all
select * from miscchgplg
)
SELECT SUM(field)
FROM sourcedta
GROUP BY field2




John Maassel
Lead Programmer/Analyst
Bob Evans Farms, Inc


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, November 27, 2012 4:22 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL SUM in complex stmt

The SQL below works perfectly. It is a combination of a JOIN, UNION, and subselect.

Along with other columns, there is one amount column in the result.


select * from miscchg A
where exists
(select * from miscchgplg b
where
a.ASHPNBR = b.ashpnbr
and a.ABLDNGD = b.ABLDNGD )
union all
select * from miscchgplg


How can I aggregate the result set by GROUPing by a key field and SUMming the amounts in each record?


Would I be better off CREATING a new temp table and then doing the grouping as a 2 step process?

(for simplicity and self-doc)

Thanks


______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.