SQL Group by field

[SBSP]

Hi I need the following

Total Items items ordered but not shipped yet.

so from the order side of the DB you have 2 tables a header then a detail table
the header contains fields such as Customer, Order Date ect ect.

The Detail table contians further detail with order lines

Eg
[item number][ Description ] [QTY ordered], [Qty Shipped ], [Price] ect...

The 2 tables get linked by its unique system generated sequence key.
I created a crystal report and copied the SQL querry.

Code: Select all

SELECT
    OEINVH."CUSTOMER", OEINVH."ORDDATE",
    OEINVD."ITEM", OEINVD."QTYORDERED", OEINVD."QTYSHIPPED"
FROM
    { oj "SQLDB1"."dbo"."OEINVH" OEINVH INNER JOIN "SQLDB1"."dbo"."OEINVD" OEINVD ON
        OEINVH."INVUNIQ" = OEINVD."INVUNIQ"}
WHERE
    OEINVH."ORDDATE" >= 20080301 AND
    OEINVH."ORDDATE" <= 20080331
ORDER BY
    OEINVD."ITEM" ASC,
    OEINVH."CUSTOMER" ASC

i now need to get the subtotal of field OEINVD."ITEM"

so I need to group by item first then group by Customer.
then have the subtotal of OEINVD."ITEM" basically the 'grand total' per item number.

After that i need to copy it into my existing stored proc and set

@field21 = the subtotal.

Questions how do i use group am i going into the correct direction ?

Tnx

[endev8003]

The columns you use in the Group By is all the columns in the select, except the aggregate columns. In other words, all the columns that doesn't have COUNT, SUM, AVG, etc.

In your sql statement, it looks like OEINVD."QTYORDERED" and OEINVD."QTYSHIPPED" will be used in SUM fields. Thus your group by will be OEINVH."CUSTOMER", OEINVH."ORDDATE" and
OEINVD."ITEM"

The result will be sum of the ordered and shipped fields, per customer, per order date, per item.

[SBSP]

Hey i got it workin i dont need to group by item then by Customer.

Code: Select all

SELECT     OEINVD.ITEM, SUM(OEINVD.QTYSHIPPED) AS Expr1
FROM         OEINVD OEINVD INNER JOIN
                      OEINVH OEINVH ON OEINVD.INVUNIQ = OEINVH.INVUNIQ
WHERE     (OEINVH.ORDDATE >= 20080301) AND (OEINVH.ORDDATE <= 20080331)
GROUP BY OEINVD.ITEM
ORDER BY OEINVD.ITEM

Now the rest of the task i think is going to be a prob

Okay How do i set a variable = to the resault of a querry ?

SELECT @FIELD21NUM = OEINVD.ITEM, SUM(OEINVD.QTYSHIPPED) AS Expr1
FROM OEINVD OEINVD INNER JOIN
OEINVH OEINVH ON OEINVD.INVUNIQ = OEINVH.INVUNIQ
WHERE (OEINVH.ORDDATE >= 20080301) AND (OEINVH.ORDDATE <= 20080331) and (OEINVD.ITEM = @ITEMNO)
GROUP BY OEINVD.ITEM
ORDER BY OEINVD.ITEM

Where its bold gives an error that says you can set a varable = to a select statement. ?


Edit

Ha HA got it.
SELECT @FIELD21NUM = SUM(OEINVD.QTYSHIPPED)
Syntax check successful!!!!!

Now to see how it runs.