Trying to Sum in Query

[mutiger83]

Ok, so I know this is a simple solution, but I just can't seem to get it to work...

In am trying to sum up usage data for items in an item master. I have a table that has all the possible item numbers in it (unique) then a table with usage for each month. So the January table will only contain some of the values on the item masetr, and the Feb table will only contain some.

I want to create a query that displays the item number, the usage for each month and then SUMS up the two months. If only one month is available then I want the sum to just be that one value.

I have been playing with the grouping feature in the query and it only returns a sum if there is a value present for each of the months.

Please help...very frustrated...

Thanks!

 

[r937]

what this requires is a LEFT OUTER JOIN

i think there's an "unmatched query" wizard for that

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[mutiger83]

To the best of my knowledge the unmatched query will exclude all records that match. Perhaps I didnt explain my situation...let me try this....

Table 1 (inclusive of all records in table 2 and 3)
A
B
C

Table 2
A 1
C 2

Table 3
C 3
B 2

I need a query that does this:

A 1
B 2
C 5

Thanks everyone!

 

[r937]

select t1.alpha
     , sum(dt.num) as total
  from Table1 as t1
left outer
  join (
       select alpha
            , num
         from Table2
       union all
       select alpha
            , num
         from Table3
       ) as dt
    on t1.alpha = dt.alpha
group
    by t1.alpha

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

SELECT A.[Item number], Sum(B.Usage)+Sum(C.Usage) AS SumOfUsage
FROM ([Item master] AS A
LEFT JOIN [January table] AS B ON A.[Item number] = B.[Item number])
LEFT JOIN [February table] AS c ON A.[Item number] = C.[Item number]
GROUP BY A.[Item number];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mutiger83]

Rudy,

Thank you very much for your response. I placed this code into a field in my query and get this error:

You have written a subquery that can return more than one field without using the EXISTS reserved work in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

Any thoughts?

Thanks!

 

[mutiger83]

Oops, I was placing that in the wrong spot...sorry...

Give me a second to try it when I paste it into SQL design...

Thanks!

 

[mutiger83]

Ok, now that I have built this correctly, I am getting "The MS Jet db engine cannot find the input table or query 'select alpha, num from Table 2 union all select alpha, num from Table3'. Make sure it exists and that its name is spelled correctly.

Here is what I have I as my tables:

Table1
alpha

Table2
alpha
num

Table 3
alpha
num

 

[mutiger83]

PHV

Thanks for your help too.

I tried your code and it worked but it returns

A (blank)
B (blank)
C 5

How can I get it to sum for the first 2 even though they aren't in both tables.

Thanks!!!

 

[PHV]

SELECT A.[Item number], Sum(Nz(B.Usage,0))+Sum(Nz(C.Usage,0)) AS SumOfUsage
FROM ([Item master] AS A
LEFT JOIN [January table] AS B ON A.[Item number] = B.[Item number])
LEFT JOIN [February table] AS c ON A.[Item number] = C.[Item number]
GROUP BY A.[Item number];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mutiger83]

AWESOME...it worked!!!

Thank you so very much!

 

[mutiger83]

If I wanted to add another table of usage I assume I would add:

LEFT JOIN [March table] AS D on A.[Item number] = D.[Item number]

How would I add that in?

Thanks!

 

[PHV]

FROM [highlight]([/highlight]([Item master] AS A
LEFT JOIN [January table] AS B ON A.[Item number] = B.[Item number])
LEFT JOIN [February table] AS c ON A.[Item number] = C.[Item number][highlight])
LEFT JOIN [March table] AS D on A.[Item number] = D.[Item number][/highlight]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244