Query help

  • Hi i have a table called InvMgmt which has the fields

    id (autogenerated)

    Part_no (varchar(30)

    Description (varchar(250)

    qty (numeric)

    the sample data in it are

    id Part_no Description qty

    ---------------------------

    1 1234 NULL 3

    2 1234 "scissor" 4

    3 1234 scissor -2

    now, i need a query which will give an output that is a summary of inventory count like this:

    id Part_no Description qty

    ---------------------------

    1 1234 scissor 5

    how to do it? help pls.

  • On second thaught, you'd be better off gettting the product info from the products table so that you get consistent results.

  • Select Part_no, MAX(Description) AS Description, SUM(Qty) AS Total FROM dbo.InvMgmt GROUP BY Part_no

    This assumes that descriptions is not a very important field in that report, cause you can never be sure of the description that will show.

  • Thanks Ninja's it worked well.! There is no product info table. This is the only table with all the information. this database gets populated on a daily basis from another source. thanks a lot.

  • HTH.

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

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