Count() function - 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)

Count() function

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-12-01 : 09:48:52

I am trying to find the number of patrons in each category(i.e. faculty, student, other, etc) and total number of publications checked out for that category. By category, by type of publication. This is my first database that I have made from scratch. I am not sure if i normalized it right.

Patron has patron info: fname, lname, email, patrontypeID
PatronType has: Patrontypeid, patronType
Checkout:publicationID, patronID, Checkout, DueDate, OnTime
publication: publicationID, title, and other information


SELECT     p.fname, p.lname, pt.patrontype, pb.title
FROM         patron p INNER JOIN
                      pt ON p.patronTypeID = pt.patrontypeID INNER JOIN
                      Checkout c ON p.patronID = c.patronID INNER JOIN
                      publication pb ON c.publicationID = pb.publicationID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:52:16

[code]SELECT pt.patrontype,COUNT(p.fname) AS patrons,COUNT(pb.publicationID) AS Publications
FROM patron p INNER JOIN
pt ON p.patronTypeID = pt.patrontypeID INNER JOIN
Checkout c ON p.patronID = c.patronID INNER JOIN
publication pb ON c.publicationID = pb.publicationID
GROUP BY pt.patrontype
[/code]

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-12-01 : 10:00:01

is it possible to remove the group by and use order by?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 10:02:34

quote:
Originally posted by jgonzalez14

is it possible to remove the group by and use order by?

group by & order by are for two different things. group by is for grouping while order by is for ordering results. didnt understand why you want replace one by other. you can always use both together


SELECT      pt.patrontype,COUNT(p.fname) AS patrons,COUNT(pb.publicationID) AS Publications
FROM         patron p INNER JOIN
                      pt ON p.patronTypeID = pt.patrontypeID INNER JOIN
                      Checkout c ON p.patronID = c.patronID INNER JOIN
                      publication pb ON c.publicationID = pb.publicationID
GROUP BY pt.patrontype
ORDER BY pt.patrontype

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources