Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: One to many data

    help requested.

    Thanks in advance.

    i have a database containing two tables:
    1. table contains item id's and some other infos (key is item id)
    2. table contains item stock holdings (item, quantity, situation[new, used, transit], place)

    i want to retreive items with their sum of quantities of each situation (including items having no quantity in any kind) like:

    item id situation quantity
    12 new 9
    12 used 3
    34 new 4
    34 trans 1
    56 null null
    57 null null

    what should be the SQL?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What have you covered in class so far? What have you actually tried so far? Those questions will help us to help you with your homework.

    My first NZDF suggestion would be:
    Code:
    SELECT  Coalesce(foo.[item id], bar.[item id]) AS [item id]
    ,  foo.situation COLLATE Latin1_General_BIN
    ,  Sum(CAST(foo.quantity AS FLOAT))
       FROM foo AS bar
       FULL OUTER JOIN bar AS foo
          ON (foo.[item id] = bar.[item id])
       GROUP BY Coalesce(foo.[item id], bar.[item id])
    ,  foo.situation COLLATE Latin1_General_BIN
       ORDER BY foo.place DESC, Sum(CAST(foo.quantity AS FLOAT)) DESC
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select items.id
         , items.otherinfo
         , situationquantities.situation
         , situationquantities.totalquantity
      from items
    left outer
      join ( select item
                  , situation
                  , sum(quantity) as totalquantity
               from stockholdings
             group
                 by item
                  , situation  ) as situationquantities
        on situationquantities.item = items.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2008
    Posts
    2
    thanks a lot.

Posting Permissions

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