Select Accum

[RSX02]

Hi
Somebody told me about a command that can cumul a value in a field in a select
For example. I want to accumulate the qty of my item where the ship_date is the same and the Item are the same also
So if I have an order 123 that has the item cp-10000 with qty of 3. I ship it.
After that I ship another order on the same day that is 124 and this order also have the item cp-10000 but with a qty of 5.
I would like to cumul the qty to have a total of 8.
Does it have a Select accum or something like that that allow me to do this kind of thing?
Thanks in advance

 

[HyperRon]

Try the Sum Function:

Select Sum(qty) from Table where Item = 'cp-10000'

I think that's what you're looking for.

 

[RSX02]

HyperRon
Thank you to took the time to answer to my question. I guess that it's what I needed.
I have another question. I am begginer with SQL.
I would like to cumulate the qty_ship for each order where my ship_date and my item are the same between each others. Do you have an idea how could I do my select?
Thanks in advance

 

[HyperRon]

I'm not sure what you're asking but, maybe this example will help? If not, please try expand your inquiry.

select sum(qty_ship) from table where ship_date (between '01/01/02' and '12/31/02') and item = 'cp-10000'

 

[RSX02]

I think is more something like that
Could you tell me if it makes sense?

SELECT @accum = sum(coitem.qty_shipped)
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country
group by co_ship.ship_date, coitem.item

 

[HyperRon]

I think you only want to group by coItem.item since you're only getting back sums for one ship date ("where co_ship.ship_date = @shippingdate&quot. Everything looks ok though.

your result will be the sum of the qty_shipped for a specified date and country.

 

[RSX02]

okay that seems okay. The only thing is that I already have a select in my Stored procedure.
How can I merge both together. When I tried to merge it, it gives me an error as co_num is invalid because it is not in an aggrecate function or in the group by.

Here is the code that I already have in my SP.
CREATE procedure mods_rpt_commercialinvoice(
@shippingdate datetype = null,
@country countrytype = null
)
as

declare
@accum as QtyUnitType

SELECT co_ship.[co_num], co_ship.ship_date, coitem.co_line , coitem.qty_shipped ,item.u_m, item.item,item.description , item.uf_country_of_origin,item.uf_int_export, item.uf_us_export,
(SELECT TOP 1 itemprice.unit_price1 FROM itemprice WHERE itemprice.item = coitem.item AND itemprice.curr_code = 'USD' AND effect_date <= GETDATE() ORDER BY effect_date DESC) as most_recent_price
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
INNER JOIN itemprice
ON (itemprice.item=coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country

I would like to merge it to the code below:
SELECT co_ship.ship_date, coitem.item, accum = sum(coitem.qty_shipped)
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country
group by co_ship.ship_date, coitem.item

Thanks in advance

 

[MDXer]

add co_num to your group by clause

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[RSX02]

but I don't want to group my co_num. It gives me this error for each field in the select: co_ship.ship_date, coitem.co_line , coitem.qty_shipped ,item.u_m, item.item,item.description , item.uf_country_of_origin,item.uf_int_export, item.uf_us_export,
I can't put it in my group. I only want to group my ship_date and Item

If I put it separatly, I don't have the accum output. Maybe I can put it separatly but I must have the output of the accum.

Thanks in advance
BTW I really appreciate the time that you put here for my thread.