SQL help

[Davegod75]

mysql database. Table is called transactions

Amount | Date(yyyy-mm-dd) | Category
---------------------------------------
23.00 | 2006-05-06 | category1
34.32 | 2006-07-06 | category2
234.23 | 2006-01-01 | category1
12.31 | 2006-05-19 | category3
41.12 | 2006-05-31 | category2

So I want to be able to Sum up all the values from amount for a particular category and for a certain month + year.

Have this but it is not working

SELECT SUM(`Amount`),`Category`,`Date` FROM `transactions` WHERE `Category` = 'Gas' GROUP BY `Category` and `Date` > 20060701 and `Date` < 20060731;

 

[MrChad]

Try:

SELECT SUM(Amount)
FROM transactions
WHERE Category = 'Gas' AND
Date > 20060701 AND
Date < 20060731

You can use GROUP BY to return a list of sums by category:

SELECT Category, SUM(Amount)
FROM transactions
WHERE Date > 20060701 AND
Date < 20060731
GROUP BY Category

 

[KLin]

nvm

 

[Davegod75]

hmm...mysql stores my dates as 2006-08-23. I think handling that is where i'm going wrong. sorry for not mentioning that

 

[KLin]

Originally posted by: Davegod75
hmm...mysql stores my dates as 2006-08-23. I think handling that is where i'm going wrong. sorry for not mentioning that

Then you probably need single quotes around the dates, ie WHERE date > '2006-07-01' AND date < '2006-07-31'