CODEWITHSUNDEEP
sqljoin
wouter
wouter

Reputation: 67

SQL query to get the same set of results

This should be a simple one, but say I have a table with data like this:

|    ID    |    Date      |    Value   |
|     1    | 01/01/2013   |      40    |
|     2    | 03/01/2013   |      20    |
|     3    | 10/01/2013   |      30    |
|     4    | 14/02/2013   |      60    |
|     5    | 15/03/2013   |      10    |
|     6    | 27/03/2013   |      70    |
|     7    | 01/04/2013   |      60    |
|     8    | 01/06/2013   |      20    |

What I want is the sum of values per week of the year, showing ALL weeks.. (for use in an excel graph) What my query gives me, is only the weeks that are actually in the database.

Upvotes: 1

Views: 58

Answers (1)

Linger
Linger

Reputation: 15048

With SQL you cannot return rows that don't exist in some table. To get the effect you want you could create a table called WeeksInYear with only one field WeekNumber that is an Int. Populate the table with all the week numbers. Then JOIN that table to this one.

The query would then look something like the following:

SELECT w.WeekNumber, SUM(m.Value) 
FROM MyTable as m
  RIGHT OUTER JOIN WeeksInYear AS w 
  ON DATEPART(wk, m.date) = w.WeekNumber
GROUP BY w.WeekNumber

The missing weeks will not have any data in MyTable and show a 0.

Upvotes: 2

Related Questions