|
|
You're in the
sql server mseq
group:SUBSELECT COUNTING ISSUE
sql server mseq:
AUTHORS BOOKS SW_BOOK_AUTHOR_JUNCTION this is a junction table joining authors to books as 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", "AUTHORS"."AUTHOR_ID", "BOOKS"."BOOK_ID", ({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}, RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL, (SELECT COUNT("BOOKS"."BOOK_ID") 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' AND "AUTHORS"."AUTHOR_ID" = "AUTHORS"."AUTHOR_ID" ) 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"))}
Vishal
first off i want to say thank you for the time you spent to assist me it is greatly appreciated. your solution is real close here is a sample of what that gives me author ! count of books --------!----------------- d.jones ! 1 d.jones ! 1 d.jones ! 1 d.lee ! 1 d.lee ! 1 d.lee ! 1 d.lee ! 1 d.lee ! 1 d.lee ! 1 d.lee ! 1 d.lee ! 1 before i put your statement edit in author ! count of books --------!----------------- d.jones ! 10 d.lee ! 10 what im trying to do author ! count of books --------!----------------- d.jones ! 3 d.lee ! 7 now i ended up switching the main select to a distinct so i only got one author but i still get the count of 1. this may be because of the 3 table junction? id post a DLL/Sample but im really new to this and don't know how that is done? [quoted text, click to view] "Vishal Parkar" <_vgparkar@yahoo.co.in> wrote in message news:uoHkliMnDHA.3700@TK2MSFTNGP11.phx.gbl... > Dan, > > Pls post DDL/sample records to get correct solution. In the query that you've written there is no > need to have unncessary joins. However you can try following query. > (untested) , pls check the syntaxes and commas. > SELECT > "AUTHORS"."LAST_NAME", > "AUTHORS"."FIRST_NAME", > "AUTHORS"."AU_STATUS", > "AUTHORS"."AUTHOR_ID", > "BOOKS"."BOOK_ID", > ({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}, > RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL, > (select count(*) > from "daniellukefield"."danie3686"."BOOKS" "BOOKS" > where "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ID") 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"))} > > If this doesn't meet the required pls post ddl/sample records. > > -- > - Vishal > > >
what i finally ended up with was much simpler that what i started 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"))} and this works thanks for everyones help. Dan [quoted text, click to view] "Vishal Parkar" <_vgparkar@yahoo.co.in> wrote in message news:uoHkliMnDHA.3700@TK2MSFTNGP11.phx.gbl... > Dan, > > Pls post DDL/sample records to get correct solution. In the query that you've written there is no > need to have unncessary joins. However you can try following query. > (untested) , pls check the syntaxes and commas. > SELECT > "AUTHORS"."LAST_NAME", > "AUTHORS"."FIRST_NAME", > "AUTHORS"."AU_STATUS", > "AUTHORS"."AUTHOR_ID", > "BOOKS"."BOOK_ID", > ({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}, > RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL, > (select count(*) > from "daniellukefield"."danie3686"."BOOKS" "BOOKS" > where "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ID") 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"))} > > If this doesn't meet the required pls post ddl/sample records. > > -- > - Vishal > > >
Dan,
Pls post DDL/sample records to get correct solution. In the query that you've written there is no need to have unncessary joins. However you can try following query. (untested) , pls check the syntaxes and commas. SELECT "AUTHORS"."LAST_NAME", "AUTHORS"."FIRST_NAME", "AUTHORS"."AU_STATUS", "AUTHORS"."AUTHOR_ID", "BOOKS"."BOOK_ID", ({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}, RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL, (select count(*) from "daniellukefield"."danie3686"."BOOKS" "BOOKS" where "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ID") 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"))} If this doesn't meet the required pls post ddl/sample records. -- - Vishal
Dan,
Excellent, As i've told you in earlier mail as well that there is no need to have unncessary joins. This seems to be tidy and hope it solves your problem. -- - Vishal [quoted text, click to view] "Dan Fields" <lanfield@ADELPHIA.NET> wrote in message news:P8hnb.54$Y26.53202@news2.news.adelphia.net... > what i finally ended up with was much simpler that what i started 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"))} > > > and this works thanks for everyones help. > > Dan > > > > "Vishal Parkar" <_vgparkar@yahoo.co.in> wrote in message > news:uoHkliMnDHA.3700@TK2MSFTNGP11.phx.gbl... > > Dan, > > > > Pls post DDL/sample records to get correct solution. In the query that > you've written there is no > > need to have unncessary joins. However you can try following query. > > (untested) , pls check the syntaxes and commas. > > SELECT > > "AUTHORS"."LAST_NAME", > > "AUTHORS"."FIRST_NAME", > > "AUTHORS"."AU_STATUS", > > "AUTHORS"."AUTHOR_ID", > > "BOOKS"."BOOK_ID", > > ({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}, > > RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL, > > (select count(*) > > from "daniellukefield"."danie3686"."BOOKS" "BOOKS" > > where "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ID") 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"))} > > > > If this doesn't meet the required pls post ddl/sample records. > > > > -- > > - Vishal > > > > > > > >
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |