Need Help with Grouping

[civilwarjunky]

I am somewhat new to SQL and need help grouping my qurrey.

select Trandate as 'Order Date',
artran.invtid as 'item #',inventory.descr as 'discription',artran.qty as 'Qty'
from artran,Inventory,Customer,Salesperson,custclass
where artran.invtid=Inventory.invtid
and artran.custid=customer.custid
and custclass.classid=customer.classid
and customer.billstate='ca'
and not customer.custid in ('calmkt','oosmkt,CALREP')-- These codes are for in house use
and trandate between '2004-07-01' and '2005-01-12'
and Salesperson.slsperid=*artran.slsperid
order by artran.Invtid


I would like to group everything by item number and have a total quantity for each item. Can you help

 

[mrdenny]

I think this is what you are looking for.

select Trandate as 'Order Date',
    artran.invtid as 'item #',
    inventory.descr as 'discription',
    [COLOR=red]SUM([/color]artran.qty[COLOR=red])[/color] as 'Qty'
from artran,
    Inventory,
    Customer,
    Salesperson,
    custclass
where artran.invtid=Inventory.invtid
    and artran.custid=customer.custid
    and custclass.classid=customer.classid
    and customer.billstate='ca'
    and not customer.custid in ('calmkt','oosmkt,CALREP')-- These codes are for in house use
    and trandate between '2004-07-01' and '2005-01-12'
    and Salesperson.slsperid=*artran.slsperid
[COLOR=red]group by Trandate, 
    artran.invtid,
    inventory.descr[/color]
order by artran.Invtid

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ESquared]

and did you mean for the second two codes of your customer ID to have a comma in them, or were there supposed to be more single quotes in there?

'oosmkt,CALREP'

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k