Post reply

Creating a veiw with a sum

  • January 28, 2009 at 11:42 am

    #217969

    I am creating a view where I can have the sum of the quantity of a part number per location.

  • January 28, 2009 at 2:38 pm

    #934222

    Cool... do you have a question?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 28, 2009 at 2:47 pm

    #934236

    Sorry, my question is how do I get the sum of the part numbers (varchar) in locations (varchar). Here is my code so far.

    SELECT TOP 100 PERCENT dbo.STOCKWH.PARTNUMBER, dbo.STOCKWH.LOCATION, dbo.STOCKWH.QTY

    FROM dbo.STOCKWH INNER JOIN

    dbo.STOCK ON dbo.STOCKWH.PARTNUMBER = dbo.STOCK.PARTNUMBER AND dbo.STOCKWH.SUBP = dbo.STOCK.SUBP

    WHERE (dbo.STOCKWH.WHCODE <> '75') AND (dbo.STOCKWH.WHCODE <> 'tool') AND (dbo.STOCKWH.WHCODE <> '50')

    GROUP BY dbo.STOCKWH.PARTNUMBER, dbo.STOCKWH.LOCATION, dbo.STOCKWH.QTY

    ORDER BY dbo.STOCKWH.LOCATION

    I need the quantity of part numbers in locations.

  • January 28, 2009 at 2:56 pm

    #934243

    Try replacing "dbo.STOCKWH.QTY" with "SUM(dbo.STOCKWH.QTY) as SumQty" in the Select clause. See if that gives you what you need. Oh, and remove that column from the Group By clause.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply