SQL How to show '0' value for a month, if

First of all my result looks like this:
| KONTONR | Month | SELSKAPSKODE | BELOP |
| 459611 ----| 1 ------| BAGA -------------- | 156000 |
| 459611 ----| 2 ------| BAGA -------------- | 73000 --|
| 459611 ----| 4 ------| BAGA ---------------| 217000-|
| 459611 ----| 5 ------| BAGA ---------------| 136000-|
| 459611 ----| 1-------| CIVO --------------- | 45896 --|
| 459611 ----| 3 ------| CIVO ----------------| 32498 --|
| 459611 ----| 4 ------| CIVO ----------------| 9841 --- |
| 330096 ----| 1 ------| BAGA ---------------| 42347 --|
| 330096 ---| 3 -------| BAGA ---------------| 3695 ---|
| ETC.

I'm trying to show month 2 month bookings on several accounts, per account (KONTONR) there are several codes (SELSKAPSKODE) on which bookings are recorded (the sum of the bookings as BELOP). I would like to give an overview of the sum of the bookings (BELOP) per account (KONTONR) per month per code (SELSKAPSKODE). My problem is the codes don't show in a month if no bookings are made on that code. Is there a way to fix this? I understand why the codes don't show, since they're simply not in the table I'm querying. And I suspect that the sollution is in making a 'fake' table which I then join (left outer join?) with 'another' table.
I just can't get it to work, I'm pretty new to SQL... Can someone please help?
My query looks like this (I only inserted the 'nested' query to make a set-up for a join, if this makes sence?!):

SELECT        TOP (100) PERCENT KONTONR, Month, SELSKAPSKODE, BELOP  
FROM            (SELECT        SELSKAPSKODE, KONTONR, SKIPS_KODE, MONTH(POSTDATO) AS  
Month, SUM(BELOP) AS BELOP  
FROM            dbo.T99_DETALJ  
WHERE        (POSTDATO >= '2012-01-01') AND (BILAGSART = 0 OR BILAGSART = 2)                       
GROUP BY SELSKAPSKODE, KONTONR, SKIPS_KODE, MONTH(POSTDATO)) AS T99_summary  
GROUP BY KONTONR, SELSKAPSKODE, Month, BELOP  
ORDER BY KONTONR, SELSKAPSKODE, Month

So concluding I would like to 'fill up' the missing months (see table at the start), for instance for account (KONTONR) 459611 month 3 is 'missing'. I would like to show month 3, with the sum of the bookings (BELOP) as '0'. Any help is greatly appreciated, thanks in advance!

回答1:

You can query a table with the values 1-12 and left outer join your result.

Here is a sample using a table variable instead of your query and a CTE to build a table with numbers.

declare @T table
(
  Month int
)

insert into @T values(1)
insert into @T values(1)
insert into @T values(1)
insert into @T values(3)
insert into @T values(3)

;with Months(Month) as
(
  select 1
  union all
  select Month + 1
  from Months
  where Month < 12
)
select M.Month,
       count(T.Month) Count,
       isnull(sum(T.Month), 0) Sum
from Months as M
  left outer join @T as T
    on M.Month = T.Month
group by M.Month

Result:

Month       Count       Sum
----------- ----------- -----------
1           3           3
2           0           0
3           2           6
4           0           0
5           0           0
6           0           0
7           0           0
8           0           0
9           0           0
10          0           0
11          0           0
12          0           0

回答2:

if you don't want to do all that you could also modify this: SUM(BELOP) with this: Sum (case when BELOP is not null then 1 else 0 end)