RE: SQL help

["John Arnold \(MFS\)" <jarnold@xxxxxxxxxxxxxxxxxxxxxxxxxx>]

You need to add a sum() function to accumulate your AmtPaid and the
Count(*) function to count your transactions as below:

Select PayDate, Store, Mfg, PdType, PmtType, sum(AmtPaid, Count(*)from
detailfile Where Paydate = 20080423 
Group by PayDate, Store, Mfg, PdType, PmtType 
Order by Paydate, Store, Mfg, PdType, PmtType


John Arnold
(301) 354-2939
 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
smorrison@xxxxxxxxxxxxxxxxxxx
Sent: Thursday, April 24, 2008 10:12 AM
To: Midrange mailing list
Subject: SQL help

I'm trying to create a sales summary file using SQL, and I'm over my
head. 
 I hope someone here can help.

I need to select all sales for a selected date and summarize it into
another file. I need to accumulate the sales amount, and create a count
of transactions by type. The fields from the detail file are: 
Field Name  - Description
PayDate - Date
Store - Store
Mfg - Manufacturer
PdType - Product Type
PmtType - Payment Type
AmtPaid - Amount paid 

Each of these fields should be in the summary file, along with a field
for a count of transactions for each type, and AMTPAID should be the
total of payments for each transaction type.

I think the SQL should start with something like:
Update summaryfile when
(Select PayDate, Store, Mfg, PdType, PmtType, AmtPaid from detailfile
Where Paydate = 20080423 Group by PayDate, Store, Mfg, PdType, PmtType,
AmtPaid Order by Paydate, Store, Mfg, PdType, PmtType)

But how do I tell SQL to add a count of the transactions? 

Thanks for your assistance,
Steve


Steven Morrison
Fidelity Express 


Confidentiality Statement: This electronic mail transmission is
confidential, may be privileged and should be read or retained only by
the intended recipient. If you have received this transmission in error,
please notify the sender immediately and delete it from your system.
--
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.