sql subselect count issue - need help -- sql server clients

Keith Kratochvil
10/27/2003 3:34:07 PM

I feel that a simple select with a GROUP BY is just what you need. =
However the solution is so simple, so perhaps I am simplifying things =
too much, or I don't fully understand your question.

Does this give you what you want?

SELECT A.Author count(*) AS TheCount
FROM Authors A JOIN SW_BOOK_AUTHOR_JUNCTION B ON A.AUTHOR_ID =3D =
B.AUTHOR_ID
WHERE A.AU_STATUS =3D 'ACTIVE'
GROUP BY A.Author

--=20
Keith

[quoted text, click to view]

Dan Fields
10/27/2003 7:12:22 PM

I have 3 tables

AUTHORS
BOOKS
SW_BOOK_AUTHOR_JUNCTION this is a junction table joining authors to books
and this is a many to many relationship

what i want to do is list my authors and a count of how many books they have
each authored.
author ! count of books
--------!-----------------
j.smith ! 24
d.jones ! 3
d.lee ! 7

and so on

what i end up with is this

author ! count of books
--------!-----------------
j.smith ! 34
d.jones ! 34
d.lee ! 34

i cant seem to get the subselect to count books for only that author.
forgive me but im very new at sql any help is appreciated. my sql is below
im using ms sql server 2000

SELECT
"AUTHORS"."LAST_NAME", "AUTHORS"."FIRST_NAME", "AUTHORS"."AU_STATUS",
"BOOKS"."BOOK_ID",
({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')},
RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL,
(SELECT COUNT("BOOK_ID") FROM "daniellukefield"."danie3686"."BOOKS" "BOOKS")
AS TOTAL_BOOKS

FROM
("daniellukefield"."danie3686"."AUTHORS" "AUTHORS"
LEFT OUTER JOIN "daniellukefield"."dbo"."SW_BOOK_AUTHOR_JUNCTION"
"SW_BOOK_AUTHOR_JUNCTION"
ON "AUTHORS"."AUTHOR_ID"="SW_BOOK_AUTHOR_JUNCTION"."AUTHOR_ID")
INNER JOIN "daniellukefield"."danie3686"."BOOKS" "BOOKS"
ON "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ID"

WHERE "AUTHORS"."AU_STATUS"='ACTIVE'
ORDER BY {FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')},
RTRIM("AUTHORS"."FIRST_NAME"))}

Dan Fields
10/27/2003 10:38:54 PM

Keith

thanks you helped alot.

with slight alterations it worked great. remember im new so i dont always
see the obvious answers. here is what i finally ended up with

SELECT
{FN CONCAT(
{FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}
, RTRIM("AUTHORS"."FIRST_NAME"))}
AS AUTHOR_FULL

, count(*) AS TheCount
FROM "daniellukefield"."danie3686"."AUTHORS" "AUTHORS"
JOIN
"daniellukefield"."dbo"."SW_BOOK_AUTHOR_JUNCTION" "SW_BOOK_AUTHOR_JUNCTION"
ON "AUTHORS".AUTHOR_ID
=
"SW_BOOK_AUTHOR_JUNCTION"."AUTHOR_ID"
WHERE "AUTHORS"."AU_STATUS" = 'ACTIVE'
GROUP BY
{FN CONCAT(
{FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}
, RTRIM("AUTHORS"."FIRST_NAME"))}

[quoted text, click to view]
I feel that a simple select with a GROUP BY is just what you need. However
the solution is so simple, so perhaps I am simplifying things too much, or I
don't fully understand your question.

Does this give you what you want?

SELECT A.Author count(*) AS TheCount
FROM Authors A JOIN SW_BOOK_AUTHOR_JUNCTION B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.AU_STATUS = 'ACTIVE'
GROUP BY A.Author

--
Keith

[quoted text, click to view]

Keith Kratochvil
10/28/2003 7:43:36 AM

I am glad that I was able to point you in the right direction. You =
probably don't need all the double quotes within your code. The sql =
statement should execute fine without them. It will also be easier to =
read. Is daniellukefield the local server? If so, you don't need to =
specify it within your query.=20

--=20
Keith

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

sql server clients : sql subselect count issue - need help