CODEWITHSUNDEEP
sql-server-2008sql
CyberK
CyberK

Reputation: 1578

SQL Query to count over a specific period

I'm having a table like this one:

-------------------------------
| NrOfVisitors | Year | Month |
-------------------------------
| 320          | 2009 | 1     |
-------------------------------
| 300          | 2009 | 2     |
-------------------------------
| 150          | 2010 | 1     |
-------------------------------
| 100          | 2010 | 2     |
-------------------------------

Now I want to count the visitors untill the 1st month of 2010. When I say:

SELECT SUM(NrOfVisitors) As TotalVisitors FROM VisitorTable WHERE YEAR <= 2010 AND Month <= 1

Then I don't get the amount of visitors because then it doesn't count the visitors of 2009 month 2. So I'm missing 300 visitors with that query. So what I need is a query to count the NrOfVisitors untill 2010 month 1

Who can help me with this/

Thanks in advance!

Upvotes: 0

Views: 478

Answers (5)

iTSrAVIE
iTSrAVIE

Reputation: 834

SELECT SUM(NrOfVisitors) As TotalVisitors FROM VisitorTable WHERE YEAR <= 2010 OR (YEAR = 2010 AND MONTH = 1)

Upvotes: 0

rownage
rownage

Reputation: 2414

Shouldn't it be

SELECT SUM(NrOfVisitors) As TotalVisitors FROM VisitorTable WHERE YEAR <= 2010 AND Month >= 1 ?

I just changed Month <= 1 to Month >= 1 because, well, 2 > 1.

That way, if you have more months, and if you don't want them counted, you can just change that constraint to reflect which months you want to count.

Upvotes: 0

Paul Creasey
Paul Creasey

Reputation: 28894

In this case you can simply use OR

WHERE YEAR <= 2010 OR MONTH <= 1

To be more general something like

WHERE YEAR < 2010 OR (YEAR = 2010 AND MONTH <=1)

Upvotes: 0

xaoax
xaoax

Reputation: 141

Just remove the: AND Month <=1 ? Edit: also make the: WHERE YEAR <= 2010 to: WhERE YEAR < 2010

That should work?

Upvotes: 0

eumiro
eumiro

Reputation: 213115

Try this:

SELECT SUM(NrOfVisitors) As TotalVisitors
FROM VisitorTable
WHERE Year * 12 + Month <= 2010 * 12 + 1

Upvotes: 1

Related Questions