Very Computer

I have a table,

Orient  Growth   Qty   QtyLeft   Type   Vendor   Batch#
2off    LEC      50    0         MG4    aaa      1
2off    LEC      25    2         MG4    aaa      2
1off    LEC      20    4         MG4    aaa      3
2off    VEC      40    6         MG4    aaa      4
1off    LEC      30    3         PG4    aaa      1
1off    VEC      30    14        PG4    aaa      2
2off    LEC      50    5         PG4    bbb      1
2off    LEC      50    0         MG4    bbb      1
...

I need to query:
Type, Vendor, Orient, Growth, Sum(Qty), Sum(QtyLeft)
searched by Type and Vendor
for each Type/Vendor, the Sums are over Orient/Growth grouping

The result should be:
Type  Vendor  Orient  Growth  Sum(Qty)  Sum(QtyLeft)
MG4   aaa     2off    LEC     75        2      'sum of first 3 records
MG4   aaa     1off    LEC     20        4
MG4   aaa     2off    VEC     40        6
PG4   aaa     1off    LEC     30        3
MG4   bbb     2off    LEC     50        0
PG4   bbb     2off    LEC     50        5

I am novice in SQL. Can anyone help me with this sql syntax? Thanks a lot.

SELECT Type,Vendor,Orient ,Growth,Sum(Qty) ,Sum(QtyLeft)
From Table
WHere Type = <specify type>
   AND Vendor = <specify vendor>
Group By Type,Vendor,Orient ,Growth

Quote:> I have a table,

> Orient  Growth   Qty   QtyLeft   Type   Vendor   Batch#
> 2off    LEC      50    0         MG4    aaa      1
> 2off    LEC      25    2         MG4    aaa      2
> 1off    LEC      20    4         MG4    aaa      3
> 2off    VEC      40    6         MG4    aaa      4
> 1off    LEC      30    3         PG4    aaa      1
> 1off    VEC      30    14        PG4    aaa      2
> 2off    LEC      50    5         PG4    bbb      1
> 2off    LEC      50    0         MG4    bbb      1
> ...

> I need to query:
> Type, Vendor, Orient, Growth, Sum(Qty), Sum(QtyLeft)
> searched by Type and Vendor
> for each Type/Vendor, the Sums are over Orient/Growth grouping

> The result should be:
> Type  Vendor  Orient  Growth  Sum(Qty)  Sum(QtyLeft)
> MG4   aaa     2off    LEC     75        2      'sum of first 3 records
> MG4   aaa     1off    LEC     20        4
> MG4   aaa     2off    VEC     40        6
> PG4   aaa     1off    LEC     30        3
> MG4   bbb     2off    LEC     50        0
> PG4   bbb     2off    LEC     50        5

> I am novice in SQL. Can anyone help me with this sql syntax? Thanks a lot.

Always post table DDLs so that others can understand your datatypes,
keys, constraints etc. Based on what you are trying to do, you can
use GROUP BY along with required aggregate functions to retrieve
grouped resultsets. Here is a guess:

SELECT Type, Vendor, Orient, Growth, Sum(Qty), Sum(QtyLeft)
  FROM tbl
 GROUP BY Vendor, Type, Orient, Growth
 ORDER BY ...

--
- Anith
(Please respond only to newsgroups)