Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    12

    Unanswered: Sub Query With Subtotals

    Hello, Thank you for your help.

    I Need to produce a table with sub totals (the number of items by color in an inventory table)

    I'm trying:
    SELECT Color, ModelID,
    (SELECT COUNT(*)) AS SubTotal
    FROM dbo.Inventory_Joins
    GROUP BY Color, ModelID

    But I'm stuck

    I Need to produce

    ID Color Qty in Stock
    1 Brown 3
    2 Green 5
    Justin Case

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if ModelID is a primary key, you won't want to group on it (there's only going to be one of each, eh?) --

    select Color, count(*) as subtotal
    into NameOfYourNewTable
    from dbo.Inventory_Joins
    group by Color

    if ModelID is not the primary key, then yeah, add it into the above like this --

    select ModelID, Color, count(*) as subtotal
    into NameOfYourNewTable
    from dbo.Inventory_Joins
    group by ModelID, Color

    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •