Re: Why are there DUPES!?!?

Hi

"Marc Connolly" <marcon33_at_home.com> wrote in message news:<i%%g7.2949$e91.1187822_at_news1.sttln1.wa.home.com>...
> I am getting dupes and for the life of me can not see why. My latest guess
> at a solution is to
> create a composite key in table pn_sc that includes "sc"? I am lost and
> this is just a stab in the
> dark. Any help really appreciated!
>
> here is the query :
>
> INSERT INTO cat_sales_one
> SELECT catcode, datesold, sum(extprice) as sumextprice
> FROM mo_sold_extprice, pn_sc, category
> WHERE mo_sold_extprice.pn = pn_sc.pn
> AND pn_sc.sc = category.sc
> AND div = 'T'
> GROUP BY cat, datesold, div
>

Your result set has 3 columns, catcode and datesold and sum(extprice) but you are grouping over 3 columns
The group by div is to much.
Example:

10:28:48 > select * from xxx;

        A B C
--------- --------- ---------

        1         1         1
        1         2         1
        1         3         1
        2         3         1
        2         1         1

10:29:01 > select a,sum(c) from xxx group by a;
        A    SUM(C)
--------- ---------
        1         3
        2         2

2 rows selected.

No dupes !

10:29:05 > select a,sum(c) from xxx group by a , b ;

        A SUM(C)
--------- ---------

        1         1
        1         1
        1         1
        2         1
        2         1

oops, dupes ...

Hth
Thomas Received on Fri Aug 24 2001 - 03:34:09 CDT