January 15, 2019 at 3:41 am
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
ORSELECT 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
January 15, 2019 at 4:28 am
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_IDSELECT 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.
January 15, 2019 at 5:12 am
Thank you,
Simple, elegant and I have learned something.🙂🙂🙂
Thanks,
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply