List all Sum of every category

[Guest]

Hi, professional
I want to list the sum of each category but it fail to list those sum which
has zero value.

I have two tables in my database.
A. tblType {Field: index, name}
1 Computer
2 Travel
3 Food
4 Others
B. tblHistory {Field: date, amount, type}
1/5/2006 100 3
2/5/2006 800 1
3/5/2006 50 4
4/5/2006 10 3
5/5/2006 80 4
6/5/2006 250 1


What i want to do is to summarize the monthly amount of each category
Like this....
Computer 1050
Travel 0
Food 110
Others 130

I try to joinly select those record with Year = 2006 and Month = 5 but it
just returns sth like this...(which miss the Travel category since there is
no Travel expense at that month)
Computer 1050
Food 110
Others 130

How should i write the query in order to make it shows up?

 

[John Spencer]

Try the following untested SQL statement

SELECT TblType.Name, Sum(H.Amount)
FROM tblType LEFT JOIN
(SELECT tblHistory.*
FROM tblHistory
WHERE tblHistory.Date Between #1/1/06# And #1/31/06#) as H
ON tblType.Index = H.Type
GROUP BY TblType.Name

 

[albert]

The SQL given is GREAT!!
In the Left Join operation, if there is no record in the Right Table, the
field is null instead of a "0"
How can I append 0 in the query?


What i want is ...
Computer 1050
Travel 0
Food 110
Others 130

Now the query gives me....
Computer 1050
Travel <-----null in the second column
Food 110
Others 130

 

[John Spencer]

Use the NZ function around the sum of the field

SELECT TblType.Name, NZ(Sum(H.Amount),0) as Total
FROM ...

 

[albert]

GREAT!!!Thank you very much.

Use the NZ function around the sum of the field

SELECT TblType.Name, NZ(Sum(H.Amount),0) as Total
FROM ...