Re: group by problems

Your group by is on status, that means ALL the other columns need a SUM or a COUNT or similar.
You could try to resolve it in this way (taking a different example)

select d.deptno, d.deptname, totsal
from dept d
, (select deptno

   , sum(salary) totsal
  from emp
  group by deptno) e
where d.deptno = e .deptno

This example is using the 'inline view' concept and it shows you the combination of aggregate and non-aggregate columns

Hth,

Sybrand Bakker, Oracle DBA

<hajir_at_my-deja.com> wrote in message news:8cgcp8$sc5$1_at_nnrp1.deja.com...
> I cannot get oracle to accept this query with the group by. any
> suggestions?
>
> SELECT A.PICKTICKET_STATUS STATUS,
> A.TOTAL_QUANTITY -A.QUANTITY_REMOVED -A.QUANTITY_CANCELED
> H_QTY_TO_SHIP,
> A.QUANTITY_PICKED H_QTY_PICKED ,A.QUANTITY_SCANNED H_QTY_SCANNED ,
> A.QUANTITY_SHIPPED H_QTY_SHIPPED,
> sum(B.TOTAL_QTY - B.REMOVED_QTY - B.CANCELED_QTY ) D_QTY_TO_SHIP,
> sum (B.PICKED_QTY) D_QTY_PICKED,
> sum(B.SCANNED_QTY) D_QTY_SCANNED,
> sum(B.SHIPPED_QTY) D_QTY_SHIPPED
> FROM PICK_TICKET A, PICK_TICKET_DTL B
> WHERE A.WHSE = '&&whse'
> and A.pickticket_no = '&&pickticket_no'
> and b.whse = a.whse
> and b.problem_no = a.problem_no
> and b.trailer_seq_no = a.trailer_seq_no
> group by a.pickticket_status;
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Apr 05 2000 - 23:33:08 CDT