Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Query help (Sum function)

    Hi,

    I have two tables as below.

    TableA:
    ID (Primary Key)
    Location
    Date

    TableB:
    ID (Primary Key)
    Qty

    What I am trying to do is to return a result set with the first column displaying all locations with a particular date value with the second column displaying the sum of all TableB's Qty values for that location and date.

    Thus, for each line of the resultset, if there are 2 records in TableA for a particular location and date. I want to go to TableB and sum the Qty values for the corresponding IDs in that table.

    Is it possible to do this using one SQL statement?

  2. #2
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up try this

    hai...

    Try the following code

    select ta.location, sum(qty) from ta , tb where ta.id = tb.id and ta.date = 'date_condition' group by ta.location;

    and replace the date_condition with ur own date condition..

    Do let me know whether this helped you...
    Sudar

    --
    My Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if all TableA locations are to be returned, including those with no matching rows in TableB, then it requires a LEFT OUTER JOIN
    Code:
    select a.Location
         , coalesce(sum(b.Qty),0) as Qty 
      from TableA as a
    left outer
      join TableB as b
        on a.ID = b.ID 
     where a.`Date` = 'date value' 
    group 
        by a.Location
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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