CODEWITHSUNDEEP
mysqlsql
bodesam
bodesam

Reputation: 527

How to select aggregate of values within a date range from a table

Let me just give a simplified example to illustrate what I mean.

Let's say we have a table named accounts with columns deposits, dmonth, and dyear. Select sum(deposits) will get the sum of ALL values under the deposits column. What is the SQL query to get sum of deposits for, say, period earlier than July 2016

Upvotes: 1

Views: 135

Answers (3)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Here is another way:

SELECT SUM(Deposits)
FROM   Accounts
WHERE  (dYear = 2016 AND dMonth < 7)
OR     dYear < 2016

Upvotes: 2

Tyler Roper
Tyler Roper

Reputation: 21672

Depending on how your dyear and dmonth or stored, you may need to do something like this:

SELECT SUM(deposits) as Deposits_Sum WHERE CONVERT(int, dyear+dmonth) < 201607

What i've done here is combined your dyear and dmonth to create a value that's chronologically sortable. I then convert this to an integer for comparison purposes.

Using this logic, we can find all records where this value is less than July 2016 by doing < 201607 in the WHERE clause. This final piece may require some modification if your years are not stored as yyyy and/or your months are not MM.

Upvotes: 1

Shark50521
Shark50521

Reputation: 13

Assuming dmonth contains something date time like you could do this with a where clase

Select sum(deposits) as Sum from accounts where dmonth < 07-01-16 00:00:000.000

See also:

SQL sum with condition

Upvotes: 0

Related Questions