sql query for monthly amount

Question

I have table like this,

SELECT                                  
  payment_id,amount,payment_date             
FROM payment

To get the monthly total amount, i wrote query as

SELECT
  payment_id
, SUM(amount) AS Total
, MonthName(payment_date) AS Month

FROM payment

But I am getting wrong answer in the amount table as all the values are getting added to the same month.

payment_id amount payment_date    
1          19.00  May

It would be grateful for me if somebody can help me out in this?

Answer 1

First, always include the year with the month -- presumably that is what you intend.

Second, you need a group by. I would recommend:

SELECT year(payment_date) as yyyy, monthname(payment_date) as mm,
      SUM(amount) AS Total
FROM payment p
GROUP BY year(payment_date), monthname(payment_date)
ORDER BY MIN(payment_date);

I included the ORDER BY so you will get the result set in a reasonable order (as opposed to alphabetically by month name).

Note that payment_id doesn't make sense in the GROUP BY. It is probably unique on every row in payment. MySQL will put an arbitrary value on each row. Almost any other database would generate an error if you have payment_id in the SELECT but not the GROUP BY.

Answer 2

You will have to group your data by month in order to make the sum.

SELECT SUM(amount), MONTH(payment_date) FROM payment GROUP BY payment_date

Answer 3

SELECT
  SUM(amount) AS Total,
  MonthName(payment_date) AS Month
FROM payment
GROUP BY MonthName(payment_date)

Answer 4

Since you only have SQL solution, here is a typical PSI (Pentaho-spoon) solution.

Basically, it is the same. However

• It is visual, thus easier to maintain in case you get more complicated query.
• You have no text to type, only mouse click.
• Your input may come from other sources than a single database.
• Your are one click away of a daily (or hourly) run.