  ## Message Posted: Tue, 20 Apr 2004 @ 20:36:28 GMT  < Last>>

 Subj: Group By and non-aggregated values From: Anomy Anom

<-- Anonymously Posted: Tuesday, April 20, 2004 16:13 -->

The following is the scenario of the problem -

```     TABLE 1 -

X Y Z ZZ A B C D E F a b c d
1 1 1 AB 1 1 1 1 1 1 u v u i
1 1 1 AC 1 1 2 1 1 2 u v x j
2 2 2 AB 2 2 2 2 2 2 k k e 3
2 2 2 DE 2 2 3 2 2 7 k e k e
3 3 3 BB 1 1 1 1 1 1 p n e f

WHERE X,Y,Z,ZZ are part of PK
A, B, C, D, E, F are Integer values
a, b, c, d are attributes values for the rows.
```

TABLE 2 -

```     X Y Z A B C D E F a b c d

WHERE X, Y, Z are part of PK and there is no ZZ.
A, B, C, D, E, F are Integer values
a, b, c, d are attributes values for the rows.
```

Data needs to inserted into Table 2 table from Table 1 -

```     X Y Z A B C D E F a b c d
1 1 1 2 2 3 2 2 3 u v u i
2 2 2 4 4 5 4 4 9 u v x j
3 3 3 1 1 1 1 1 1 k k e 3
```

In case there are multiple rows based on ZZ from Table 1, then insert using SUM(A), SUM(B), SUM(D), SUM(E), SUM(F) for those rows from Table 1

But the problem is to implement using SQL where the SQL should be able to SUM up the values for A, B.. ,F column if there are multiple ZZ in it.

The query -

```     INSERT INTO Table2
(
a,
b,
c,
d,
A,
B,
C,
D,
E,
F,
X,
Y,
Z
)
SELECT
a,
b,
c,
d,
SUM (A),
SUM (B),
SUM (C),
SUM (D),
SUM (E),
SUM (F),
X,
Y,
Z
FROM Table1
group by X, Y, Z, ZZ;
```

does not work because a, b, c are part of non-aggregated values.

The query needs to be executed from a proc and the source table has 10m rows. FOR Cursor processing will go on for ever for such count.

A partial Insert could be done using temp tables, but I am looking for cleaner method.

Thanks  < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback 