John John - 6 months ago 13
SQL Question

GROUP BY - add result with null group by key to other result

Title sucks, I couldn't come up with a good one explaining what I want, sorry.

I have a complex query which I have simplified to one below. Basically it always must return one record. Main table has a left join (it can't be an inner join) with another table from which it retrieves count of records it matches and so far it also returned one record, but now there is case where one property from secondary table can be null and if so, that record must also be included, but then it returns two rows. I somehow need to make them as one record where both matching records count and results that contain that null value count are summed.

So, given these tables:

TableA TableB
+----+------+ +----+------+
| id | type | | id | type |
+----+------+ +----+------+
| 1 |Email | | 1 |Email |
| 2 |Twitt | | 2 |null |
+----+------+ +----+------+


And this query:

select * from TableA a
left join
(select count(distinct b.id) count, b.type
from TableB b
Group by b.type) as asd
on a.type = asd.type or asd.type is null
where a.type = 'Email'


which returns:

+----+------+-------+------+
| id | type | count | type |
+----+------+-------+------+
| 1 |Email | 1 |(null)|
| 1 |Email | 1 |Email |
+----+------+-------+------+


is it possible to return it like this:?

+----+------+-------+------+
| id | type | count | type |
+----+------+-------+------+
| 1 |Email | 2 |Email |
+----+------+-------+------+


I actually don't even need to returns secondary's table's type just the right count.
Here is a fiddle I made: http://sqlfiddle.com/#!3/8cf5c/2

Answer

I don't know why you need the type at the end, in the case if you can remove it, the query something like this

select a.id, a.type, count(asd.count) as count
from TableA a join 
     (select count(distinct b.id) as count, b.type
      from TableB b
      Group by b.type
     ) asd
     on a.type = asd.type OR asd.type IS NULL
where a.type = 'Email'
group by a.id, a.type

But if you want exactly the same result of your expectation, I think you can try this query

select a.id, a.type, count(asd.count) as count, ISNULL(asd.type,a.type) as type
from TableA a join 
     (select count(distinct b.id) as count, b.type
      from TableB b
      Group by b.type
     ) asd
     on a.type = asd.type OR asd.type IS NULL
where a.type = 'Email'
group by a.id, a.type, ISNULL(asd.type,a.type)