| Author |
Message |
|
Robert Chapma
#1 / 9
|
 Grouping multiple rows into 1 row during a select into I have a table which looks something like this: interval value 1 value2
===== ====== =====
1 25 25
2 15 15
I need to able to select from this table into another temp table and at the
same time group the rows together, by any multiple, to look something like
this:
interval value 1 value2
===== ====== =====
2 40 40
You can see that the above row has retained the last interval value and then
did a sum on the other two columns.
Is there any SQL statement or attribute that I can use to coerce the data
into this shape?
Also, I need to be able to do on tables with millions of rows.
Any help is very much appreciated.
Cheers
Rob
|
| Fri, 03 Aug 2001 03:00:00 GMT |
|
 |
|
Trevor Dwye
#2 / 9
|
Grouping multiple rows into 1 row during a select into Hi Robert, SELECT
MAX(interval) AS interval,
SUM(value1) AS value1,
SUM(value2) AS value2
INTO
#temp_summary
FROM
interval_log
GO
Best Regards
Trevor Dwyer - SQL Server MVP
Quote:
>I have a table which looks something like this: >interval value 1 value2
>===== ====== =====
>1 25 25
>2 15 15
>I need to able to select from this table into another temp table and at the
>same time group the rows together, by any multiple, to look something like
>this:
>interval value 1 value2
>===== ====== =====
>2 40 40
>You can see that the above row has retained the last interval value and
then
>did a sum on the other two columns.
>Is there any SQL statement or attribute that I can use to coerce the data
>into this shape?
>Also, I need to be able to do on tables with millions of rows.
>Any help is very much appreciated.
>Cheers
>Rob
|
| Fri, 03 Aug 2001 03:00:00 GMT |
|
|
|
Robert Chapma
#3 / 9
|
Grouping multiple rows into 1 row during a select into Thanks for the reply, perhaps I should be clearer in what I want to do. I need to sum rows 1 and 2, then 3 and 4, then 5 and 6 etc. The regular aggregate functions don't seem to give me the ability to do this. Thanks again.
Rob
|
| Fri, 03 Aug 2001 03:00:00 GMT |
|
|
|
Janice Parkinso
#4 / 9
|
Grouping multiple rows into 1 row during a select into Well, that was a fun problem. Its not an elegant solution, but I think this does what you are trying to do:
begin
from tbl
insert into #tbl2 (interval, v1, v2)
end
-- get the last row if needed
insert into #tbl2 (interval, v1, v2)
from tbl
select * from #tbl2
Good Luck
Janice
Quote:
>Thanks for the reply, perhaps I should be clearer in what I want to do. I >need to sum rows 1 and 2, then 3 and 4, then 5 and 6 etc. The regular >aggregate functions don't seem to give me the ability to do this. >Thanks again.
>Rob
|
| Sun, 05 Aug 2001 03:00:00 GMT |
|
|
|
SDevara
#5 / 9
|
Grouping multiple rows into 1 row during a select into Hi Robert Chapman, Well if ur first column has the value in sequence, this will work
Select Case When Round(<Col1>/2,0)*2=<Col1> then <Col1>-1
Else Col
End, sum(<col2>)
From <Table>
Group by (Case When Round(<Col1>/2,0)*2=<Col1> then <Col1>-1
Else Col
End)
hope this meets ur requirement
Regards
Dev
Quote:
> Well, that was a fun problem. Its not an elegant solution, but I think this
> does what you are trying to do:
> begin
> from tbl
> insert into #tbl2 (interval, v1, v2)
> end
> -- get the last row if needed
> insert into #tbl2 (interval, v1, v2)
> from tbl
> select * from #tbl2
> Good Luck
> Janice
> >Thanks for the reply, perhaps I should be clearer in what I want to do. I
> >need to sum rows 1 and 2, then 3 and 4, then 5 and 6 etc. The regular
> >aggregate functions don't seem to give me the ability to do this.
> >Thanks again.
> >Rob
|
| Sun, 05 Aug 2001 03:00:00 GMT |
|
|
|
JRSte
#6 / 9
|
Grouping multiple rows into 1 row during a select into On Mon, 15 Feb 1999 11:28:47 -0500, "Robert Chapman" Quote:
>Also, I need to be able to do on tables with millions of rows.
I scan 5m records, do a group by producing 800k records, then walk the resultset with a cursor inserting into another table, all in about forty minutes. I was working on the same thing done without the cursor, but ran
across a bug and, although I had a workaround, dropped it. Doubt it
would be much faster.
On a dual processor Compaq with 2gigabytes of RAM.
Joshua Stern
|
| Mon, 06 Aug 2001 03:00:00 GMT |
|
|
|
Paul Thornet
#7 / 9
|
Grouping multiple rows into 1 row during a select into Quote: >On a dual processor Compaq with 2gigabytes of RAM.
>Joshua Stern
Makes my proposed configuration of 512meg RAM look very cheapskate indeed!
|
| Mon, 06 Aug 2001 03:00:00 GMT |
|
|
|
JRSte
#8 / 9
|
Grouping multiple rows into 1 row during a select into
Quote: >>On a dual processor Compaq with 2gigabytes of RAM. >>Joshua Stern >Makes my proposed configuration of 512meg RAM look very cheapskate indeed!
Some slow afternoon, I'm going to {*filter*}SQLServer down to 128mb and rerun some of my code, see what different it makes! Joshua Stern
|
| Tue, 07 Aug 2001 03:00:00 GMT |
|
|
|
JRSte
#9 / 9
|
Grouping multiple rows into 1 row during a select into
Quote: >>On a dual processor Compaq with 2gigabytes of RAM. >>Joshua Stern >Makes my proposed configuration of 512meg RAM look very cheapskate indeed!
Some slow afternoon, I'm going to {*filter*}SQLServer down to 128mb and rerun some of my code, see what different it makes! Joshua Stern
|
| Tue, 07 Aug 2001 03:00:00 GMT |
|
| |
