CODEWITHSUNDEEP
mysqlsqldatabasepentaho-spoon
Nivedita C
Nivedita C

Reputation: 1

sql query for monthly amount

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?

Upvotes: 0

Views: 2416

Answers (4)

AlainD
AlainD

Reputation: 6356

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

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 1

phoniq
phoniq

Reputation: 238

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

Upvotes: 0

Nicky
Nicky

Reputation: 468

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

Upvotes: 0

Related Questions