Writing SQL [RESOLVED]

[freeze_sr]

I'm using MS Access 2002

I have a table called Table 1

In table1 there are colums called "days", "month", & "spent"

How do i write an sql to view the total sum spent for each month. (the "spent" in my database is filled for each day - so 30days = 30spents * 12 months)


The output should be just two columns - month & spent. And it should have only 12 rows - January to december.


I'm using this for now

SELECT Sum(spent) AS January
FROM table1
WHERE (((table1.month)="January"));

But i want all the months in one query.
Is there a way to get what i want, or is making 12 queries then combining them the only way?

[salvelinus]

I wouldn't use month as a field name since it's also the name of a function in VB.
Anyway, instead of your WHERE clause, try a GROUP BY month.

[Ecniv]

To expand on salvelinus's post...

You want the montha nd a (sum) total of spend, yet in your Sql you only bring back one sum field and filter on a where.
Tru this works, but you'd need sub queries for each month.

As salvelinus posted, use a group by.

SELECT 
   [table1].[month]
   ,Sum(spent) AS Total
FROM 
   table1
GROUP BY
   [table1].[month]

When you use group by you must list all fields that are not aggregate functions (sum, max, min etc).

Try the above then mess around with the sql, see what happens.