Query help (Sum function)

barkerj · #1 (**permalink**) 11-09-04, 19:31

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?

Sudar · #2 (**permalink**) 11-10-04, 05:39

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...

r937 · #3 (**permalink**) 11-10-04, 06:33

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

Internet Services

Web Development

Digital Marketing

Consumer Tech