Re: Summarising data

From: Rohtash Kapoor (rohtash_nospam_at_sqlmantra.com)
Date: 03/29/04

  • Next message: Hugo Kornelis: "Re: Summarising data" 
    Date: Mon, 29 Mar 2004 08:45:28 -0800

    In Query Analyzer goto Query-->Results in Text, and execute the following:

    Create TABLE #MyTable
    (
      Stock_Code VARCHAR(10),
      Location VARCHAR(10),
      Quantity INT
    )

    INSERT INTO #MyTable VALUES('AD002','B1',200)
    INSERT INTO #MyTable VALUES('AD002','B2',150)
    INSERT INTO #MyTable VALUES('AD002','B3',350)
    INSERT INTO #MyTable VALUES('AD003','B1',50)
    INSERT INTO #MyTable VALUES('AD003','B2',80)
    INSERT INTO #MyTable VALUES('AD003','B3',70)

    SELECT Stock_code, Location, Quantity
    FROM #MyTable
    ORDER BY Stock_code
    COMPUTE SUM(Quantity) BY Stock_Code 

    ---
Rohtash Kapoor
http://www.sqlmantra.com
"Wes" <wharrison@adcbarcode.com> wrote in message
news:107d401c41591$8144fdc0$a001280a@phx.gbl...
> I have a table of data storing the following fields
>
> Stock_Code (varchar 10)
> Location (varchar 10)
> Quantity (int)
>
> Sample data is as follows
>
> Stock_Code     Location      Quantity
> AD002          B1            200
> AD002          B2            150
> AD002          B3            350
> AD003          B1            50
> AD003          B2            80
> AD003          B3            70
>
> I would like a query that would return all rows from the
> table but also subtotal the quantity of stock codes. i.e.
> for each change in stock code i would receive a sub total
> line displaying the total for that stock code (in this
> example, AD002 = 700, AD003 = 200)
>
> Any help would be greatly appreciated
>
> Wes.
  • Next message: Hugo Kornelis: "Re: Summarising data"