Need help on MySQL query?

Anon

Hi,

I have some problem executing a sql query in MySQL. I have a table consisting of 2 columns, date and amount. I am trying to retrieve a summary of total amount for a particular month.

Here's the psuedo code:

SELECT SUM(amount)
from Table
Where month = july/2000

What's the correct syntax for MySQL ? Thanks a lot in advance.

Anon

The WHERE clause depends on what format you have the date stored in the table. If it's in a standard format that mysql accepts like yyyy-mm-dd then you can use the mysql date functions. The mysql function you need to use is called MONTHNAME(). I haven't written sql queries in a long time so I'm not sure how to do it correctly. Try something like this

select amount from Table where MONTHNAME(date_column) = 'August';

You might be able to modify it to return the sum in one statement.

Alvin wrote:

Hi,

I have some problem executing a sql query in MySQL. I have a table consisting of 2 columns, date and amount. I am trying to retrieve a summary of total amount for a particular month.

Here's the psuedo code:

SELECT SUM(amount)
from Table
Where month = july/2000

What's the correct syntax for MySQL ? Thanks a lot in advance.

Anon

Problem might be:
You need single quotes around july/2000, such as:
SELECT SUM(amount) FROM table
WHERE month = 'july/2000';
hth
justin

Anon

I am not very familiar with MySQL, but I am
familiar with other versions of SQL and RDBMS...
Since "Sum" is an aggregate function, I think
you have to use "Group By" statement.
So,
SELECT SUM(AMOUNT)
FROM TABLE_NAME
GROUP BY MONTH
HAVING MONTH = JULY/2000;

once again, I am not familiar with MySQL yet,
but hope this helps.

Anon

Yeah, I think it's better to put the date in a standard format. Then the following query should work:

SELECT SUM(amount_column) FROM tablename WHERE YEAR(date_column)='2000' && MONTHNAME(date_column)='June';

Anon

Hi Nilesh,

The date is in standard format. I did use the syntax you told me:

SELECT SUM(amount_column) FROM tablename WHERE YEAR(date_column)=\'2000\' && MONTHNAME(date_column)=\'June\';

It worked, but it didn\'t sum the amount, it shows the amount for each day on that particular month. The results shows:

March 100
March 120
March 320
March 113
....

How is this possible?

Anon

That's odd. I don't know how that's possible. The statement should return only one result, the sum that is, not several results for each day. It works for me from the shell where I ran the query on one of my tables that had a date column and a decimal column. Try it from the shell and see what you get.