trying to select a total Number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trying to select a total Number

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-12-01 : 20:20:15

I am trying to find many books have more than one author. This Query finds it but does not give me a total number it just list 9 rows. I need it to just have one row and have 9 as the answer. Can anyone help?


select p.publicationID as NumberofBooks
from publication p 
inner join publicationAuthorJoin pa
on pa.publicationID = p.publicationID
inner join authors a 
on pa.authorID = a.authorID
group by p.publicationID
having count(a.authorID) >1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-01 : 20:38:27

select count(*) from (YourQuery) dt

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-12-01 : 20:41:44

still returns the same thing

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-01 : 23:17:21

quote:
Originally posted by jgonzalez14

I am trying to find many books have more than one author. This Query finds it but does not give me a total number it just list 9 rows. I need it to just have one row and have 9 as the answer. Can anyone help?

select count(NumberofBooks)as NumberofBooks
from
(select p.publicationID as NumberofBooks
from publication p
inner join publicationAuthorJoin pa
on pa.publicationID = p.publicationID
inner join authors a
on pa.authorID = a.authorID
group by p.publicationID
having count(a.authorID) >1)t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:09:30

[code]select count(publicationID)
from
(
select p.publicationID ,
count(a.authorID) over (partition by p.publicationID) AS AuthorCount
from publication p
inner join publicationAuthorJoin pa
on pa.publicationID = p.publicationID
inner join authors a
on pa.authorID = a.authorID
)t
where AuthorCount>1[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-02 : 15:06:34

quote:
Originally posted by jgonzalez14

still returns the same thing

That isn't possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources