June 5, 2009 at 9:09 pm
I am selecting rows from Table A, but would like to count the number of records in Table B (which has foreign key to A). I've tried the following statement,
SELECT
A.id,
A.column,
A.column3,
COUNT(B.Aid) As 'Number Of Foreign Keys' --foreign key
FROM Table A INNER JOIN Table B ON A_id = B.Aid
but got,
Column 'A.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any assistance with this will be helpful. Thanks in advance to all that reply
June 5, 2009 at 9:42 pm
Dan Fran (6/5/2009)
I am selecting rows from Table A, but would like to count the number of records in Table B (which has foreign key to A).
Hi,
Try this
Select A.ID,A.COLUMN,A.COLUMN2,
(Select count(B.ID) from TABLEB AS B where B.ID =A.ID) [Foreign key]
From TABLEA AS A
ARUN SAS
June 5, 2009 at 10:03 pm
That worked. Thank you sir. 🙂
June 7, 2009 at 8:59 am
I suggest to use a GROUP BY instead of a sub-query in your SELECT clause. It performs better for many data:
SELECT
A.id,
A.column1,
A.column3,
COUNT(B.Aid) As 'Number Of Foreign Keys' --foreign key
FROM TableA A
INNER JOIN TableB B ON A.A_id = B.Aid
GROUP BY
A.id,
A.column1,
A.column3
Flo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply