MySQL: Select * from table up to this date and sum the column amounts

1
votes

I have this table:

+----+---------------------+---------+---------+
| id | date                | amounta | amountb |
+----+---------------------+---------+---------+
| 1  | 2014-02-28 05:58:41 | 148     | 220     |
+----+---------------------+---------+---------+
| 2  | 2014-01-20 05:58:41 | 50      | 285     |
+----+---------------------+---------+---------+
| 3  | 2014-03-30 05:58:41 | 501     | 582     |
+----+---------------------+---------+---------+
| 4  | 2014-04-30 05:58:41 | 591     | 53      |
+----+---------------------+---------+---------+
| 5  | 2013-05-30 05:58:41 | 29      | 59      |
+----+---------------------+---------+---------+
| 6  | 2014-05-30 05:58:41 | 203     | 502     |
+----+---------------------+---------+---------+

I want to sum all the columns of each row where year = 2014 and up to month = 3 (Jan, Feb, Mar).

EDIT:

I do not want to select quarterly. I want to be able to select between months. From Jan to Apr, or from Mar to Sep for example.

How can I perform this?

3
mysqlsql

3 Answers

5
votes

You can use date functions, QUARTER() and YEAR()

SELECT 
  SUM(amounta),
  SUM(amountb)
FROM
  t
WHERE
  YEAR(`date`)=2014
  AND
  QUARTER(`date`)=1

That will fit quarterly query. But for interval of month, of course, use MONTH():

SELECT 
  SUM(amounta),
  SUM(amountb)
FROM
  t
WHERE
  YEAR(`date`)=2014
  AND
  MONTH(`date`)>=1
  AND
  MONTH(`date`)<=8
0
votes

try this

SELECT SUM(amounta) FROM `your_table`
WHERE date LIKE '2014-01-%' OR date LIKE '2014-02-%' OR date LIKE '2014-03-%'
0
votes

Try this

SELECT 
  SUM(Case when YEAR(`date`)=2014 AND QUARTER(`date`)=1 Then amounta else 0 end),
  SUM(Case when YEAR(`date`)=2014 AND QUARTER(`date`)=1 Then amountb else 0 end)
FROM Table1