Hi.

I have a very large table, that contains too much detailed data.

Has anyone made a sql-script that merges records?

Example:
| Date | Time | ProductNo | Amount |

Should be merge into:
| Date | ProductNo | SUM(Amount) |

Thanks.

Eydun

If I understant what you need:

1) Select data grouped

select date, productno, sum(amount) from yourtable
group by date, productno

2) Create another table with the results

select date, productno, sum(amount) into newtable from yourtable
group by date, productno

Eduardo

Hello Jose.

Thanks for the reply, and sorry for my late reply.

You are correct regarding "1) Select data grouped", but I do not want to create another table, but instead insert/delete into the same table.

Thanks,
Eydun


Jose Eduardo Helminsky wrote:

Eydun

If I understant what you need:

1) Select data grouped

select date, productno, sum(amount) from yourtable
group by date, productno

2) Create another table with the results

select date, productno, sum(amount) into newtable from yourtable
group by date, productno

Eduardo

Eydun


Looking at the first post and this one you want to change the structure of the table at the same time as merging the data (delete the Time column and change the amount into sum). That's not a good idea at all. I suggest that you follow Eduardo's approach and create a new table, then simply delete the old table and rename the new one.

Roy Lambert

Hi Roy.

Thanks for the reply.

I do not want to change the structure of the data, but just leave the "Time"-column blank.

So what I am trying to do, could be named compression or merge of records. I would expect that someone has done somethink like this below, e.g. in PointOfSale-databases, that has a lot or records?

Eydun

>I do not want to change the structure of the data, but just leave the "Time"-column blank.
>
>So what I am trying to do, could be named compression or merge of records. I would expect that someone has done somethink like this below, e.g. in PointOfSale-databases, that has a lot or records?

My personal opinion is that you're approaching this the wrong way. If you need/want a summary table create a separate table for this and leave the detail where it is. Don't try and have a mixture of summary and detail in the same table.

However, if you want to do that you can still follow the approach outlined by Eduado and myself.

1. create a temporary summary table using INSERT
2. delete the detail rows you've just transfered into the summary table
3. insert the summary rows back into the detail table
4. drop the temporary table

You can use 4 DBISAM queries to accomplish the steps or combine them into a ; separated script. If you're looking for a single piece of SQL code to accomplish what you want - forget it.

Roy Lambert

Roy, thanks for reply