Left outer join count.

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  January 15, 2019 at 3:41 am

  #416629

  Is there an elegant solution to count(*) zero if there are no rows in the 'second' table.

  SELECT COUNT(*) AS TEL, ID, A_TEXT, A_ID FROM A FULL OUTER JOIN B ON ID = A_ID GROUP BY ID, A_TEXT, A_ID
  OR
  SELECT COUNT(*) OVER (PARTITION BY A_ID) AS TEL, * FROM A FULL OUTER JOIN B ON ID = A_ID

  Instead of a tel (= count)  of 1 when there is not row in B, I would like to get the number zero.

  Ben

  
CREATE TABLE A (ID INT, A_TEXT VARCHAR(30))
INSERT INTO A VALUES(0, 'NONE')
INSERT INTO A VALUES (1, 'ONE')
INSERT INTO A VALUES (2, 'TWO')
INSERT INTO A VALUES (3, 'THREE')

  CREATE TABLE B (A_ID INT, B_TEXT VARCHAR(30), B_ID_TEXT VARCHAR(300))
INSERT INTO B VALUES (1, 'EEN','a')
INSERT INTO B VALUES (2, 'TWEE','b')
INSERT INTO B VALUES (2, 'ZWEI','c')
INSERT INTO B VALUES (3, 'DRIE','d')
INSERT INTO B VALUES (3, 'TROIS','e')
INSERT INTO B VALUES (3, '3','f')

  SELECT * FROM A
SELECT * FROM B
SELECT * FROM A FULL OUTER JOIN B ON ID = A_ID

  SELECT COUNT(*) AS TEL, ID, A_TEXT, A_ID FROM A FULL OUTER JOIN B ON ID = A_ID GROUP BY ID, A_TEXT, A_ID

  SELECT COUNT(*) OVER (PARTITION BY A_ID) AS TEL, * FROM A FULL OUTER JOIN B ON ID = A_ID

  GO
DROP TABLE A
GO
DROP TABLE B
GO

  

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  January 15, 2019 at 4:28 am

  #2018914

  Does this give you what you want?:
  SELECT COUNT(B.A_ID) AS TEL, ID, A_TEXT, A_ID FROM A FULL OUTER JOIN B ON ID = A_ID GROUP BY ID, A_TEXT, A_ID
SELECT COUNT(B.A_ID) OVER (PARTITION BY A_ID) AS TEL, * FROM A FULL OUTER JOIN B ON ID = A_ID 
  It works because NULL values aren't included in COUNT.

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  January 15, 2019 at 5:12 am

  #2018917

  Thank you,
  Simple, elegant and I have learned something.
  Thanks,
  Ben