CODEWITHSUNDEEP
sqljoingroup-by
Sasi
Sasi

Reputation: 27

SQL query for getting count on same table using left outer join

I have a table from which I need to get the count grouped on two columns.

The table has two columns one datetime column and another one is success value(-1,1,0) What i am looking for is something like this:

Count of success value for each month:

month----success-----count
11------- -1 ------- 50

11------- 1 --------- 50

11------- 0 ------- 50

12------- -1 ------- 50

12------- 1 ------- 50

12------- 0 ------- 50

If there is no success value for a month then the count should be null or zero. I have tried with left outer join as well but of no use it gives the count incorrectly.

Upvotes: 1

Views: 2286

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

You need to cross join all the available months, against the 3 success values to build a virtual matrix, which can then be left joined to the actual data

select m.month, s.success, COUNT(t.month)
from (select distinct MONTH from tbl) m
cross join (select -1 success union all select 1 union all select 0) s
left join tbl t on t.month = m.month and t.success = s.success
group by m.month, s.success

If you need missing months as well, that can be done, just slightly more complicated by changing the subquery "m" above.

@updated Count(*) will always return at least 1 for left joins. count(colname) from the right part of the left join to be correct.

Upvotes: 1

Paul Tomblin
Paul Tomblin

Reputation: 182782

You probably need a table with the just the values from 1-12 to join with so you can get a zero count.

Upvotes: 0

Related Questions