trouble with specific counts - SQL Server Forums

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)

trouble with specific counts

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-12-05 : 10:36:47

I am trying to fine the number of patrons in each category and total number of publications checked out for that category. By category, by type of publication count(pu.publication is returning the same thing as count(p.patronID)

SELECT count(p.patronID), pt.patronType, count(pu.publicationID)

FROM patron p
inner join patronType pt
on p.patronTypeID = pt.patrontypeID
inner join Checkout c
on c.patronID = p.patronID
inner join publication pu
on pu.publicationID = c.publicationID
group by pt.patronType;

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-12-05 : 10:43:58

actually this is what is up. IF "josh" has checkedout 2 books it is counting him twice. how do i not let this happen?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:07:22

[code]SELECT count(DISTINCT p.patronID), pt.patronType, count(DISTINCT pu.publicationID)

FROM patron p
inner join patronType pt
on p.patronTypeID = pt.patrontypeID
inner join Checkout c
on c.patronID = p.patronID
inner join publication pu
on pu.publicationID = c.publicationID
group by pt.patronType;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources