Create one record from multiple records

Create one record from multiple records

Post by wickedbus » Fri, 01 Sep 2006 03:20:04


Hi everyone. Can someone please give me some pointers on how to
accomplish this task? I have one table with multiple records. Looks
kind of like this:

------------------------------------------------------------------------------------------------------------------
Cust_ID Name Charge Date
1000 XXXXXXXXXX 536.10 06/01/00
1352 XXXXXXXXXX 204.19 07/13/00
1000 XXXXXXXXXX 1055.42 08/15/00
1352 XXXXXXXXXX 978.11 09/14/00
------------------------------------------------------------------------------------------------------------------
What I need to do here is take all of the cust_id's that are the same
(the cust_ID field has multiple entries, just different charges and
dates), add the sum of the charges for a given year (for this example,
dates from 01/01/00 to 12/31/00) and insert the added total into a new
dbf file as one record. For the example above:

------------------------------------------------------------------------------------------------------------------
Cust_ID Name Total Charges Year
1000 XXXXXXXXXX 1591.52 00
1352 XXXXXXXXXX 1182.30 00
-----------------------------------------------------------------------------------------------------------------

Can someone tell me what would be the easiest way to get this done?
There are about 95,xxx records in this table.

Any help would be appreciated.

Arvin
 
 
 

Create one record from multiple records

Post by Bernhard S » Fri, 01 Sep 2006 03:35:18

Hi Arvin,


SELECT cust_id, name, sum(charge) as charges, year(date) as year ;
FROM table ;
INTO TABLE sumtable ;
GROUP BY cust_id, name, 4

Regards
Bernhard Sander

 
 
 

Create one record from multiple records

Post by wickedbus » Fri, 01 Sep 2006 08:46:22

hank you for the help. I appreciate it. I just have one more question.
Some of my records have a cust_it, but the Name field was left blank.
If I run the code below, it does what I want it to do, but will create
a separate record for the records that have the name field blank. For
example:
------------------------------------------------------------------------------------------------------------------
Cust_ID Name Charge Date
1000 John Doe 536.10 06/01/00
1000 John Doe 100.00 01/01/00
1352 Jane Doe 204.19 07/13/00
1352 Jane Doe 100.00 01/01/00
1000 (blank) 1055.42 08/15/00
1352 (blank) 978.11 09/14/00
------------------------------------------------------------------------------------------------------------------

When I run the code, I will get:
------------------------------------------------------------------------------------------------------------------
Cust_ID Name Total Charges Year
1000 John Doe 636.00 00
1352 Jane Doe 304.19 00
1000 (blank) 1055.42 00
1352 (blank) 978.11 00
------------------------------------------------------------------------------------------------------------------
Is there anything I can do so that the prg doesn't separate the records
with blank Name's from the records with names even though the Cust_ID
is the same?

Thanks for all your help.

Arvin


Bernhard Sander wrote:

 
 
 

Create one record from multiple records

Post by Olaf Dosch » Fri, 01 Sep 2006 09:00:53

Hi,

several options.
1. first find such inconsistencies and correct them

2. use this modified select, that assumes "Jon Doe">Space(n):
SELECT cust_id, Max(name), sum(charge) as charges, year(date) as year ;
FROM table ;
INTO TABLE sumtable ;
GROUP BY cust_id, 4

pray that the max(name) value is the correct spelling

3. leave out the name field and add it later by finding the most often
stored name for each cust_id:
SELECT cust_id, sum(charge) as charges, year(date) as year ;
FROM table ;
INTO TABLE sumtable ;
GROUP BY cust_id, 3

4. join the name from a customers table.

Bye, Olaf.
 
 
 

Create one record from multiple records

Post by AA » Fri, 01 Sep 2006 23:39:11

hat's what happens when you don't respect the rules of 'normal forms' for
relational databases. The Name column in the Charges table just should not
be there at all, it's redundant and leads to duplication errors. Cust_id in
this table is a Foreign Key and points to another table where Cust_id is the
unique Primary Key and Name is stored once and for all.

SELECT A.cust_id, B.name, SUM(A.charge) , MIN(date), MAX(date) ;
FROM Customers AS B JOIN Charges AS B ON A.cust_id=B.custid;
WHERE date BETWEEN DATE(2005,1,1) AND DATE(2005,12,31) ;
GROUP BY B.cust_id, A.name

-Anders

meddelandet news: XXXX@XXXXX.COM ...


 
 
 

Create one record from multiple records

Post by Olaf.Dosch » Sat, 02 Sep 2006 00:16:50

> SELECT A.cust_id, B.name, SUM(A.charge) , MIN(date), MAX(date) ;

Charges as A

To extend the SQL by Bernhard, this would be:

SELECT A.cust_id, B.name, sum(A.charge) as charges, year(A.date) as year ;
FROM table As A LEFT JOIN customers As B on A.cust_id = B.cust_id;
INTO TABLE sumtable ;
GROUP BY cust_id, 4

Bye, Olaf.