Help with query using two counts

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

General SQL Server Forums

New to SQL Server Programming

Help with query using two counts

Author

Topic

juan11041970
Starting Member

2 Posts

Posted - 2011-08-01 : 16:55:14

The following query:

select distinct (a.name),
(select count(*) from test.books where author_id = b.author_id) as TOTAL_BOOKS,
count(b.numpages) as PROLIFIC
from test.authors a, test.books b
where a.idauthors = b.author_id
and b.year_written >= 1960
group by b.numpages
having b.numpages > 1000

Yields the following result:

+ --------- + ---------------- + ------------- +
| name | TOTAL_BOOKS | PROLIFIC |
+ --------- + ---------------- + ------------- +
| John Doe | 2 | 1 |
| Robert Jones | 4 | 2 |
| Susan Jones | 3 | 1 |
| Robert Jones | 4 | 1 |
+ --------- + ---------------- + ------------- +
4 rows

What I need is a query that will yield the following result:

+ --------- + ---------------- + ------------- +
| name | TOTAL_BOOKS | PROLIFIC |
+ --------- + ---------------- + ------------- +
| John Doe | 2 | 1 |
| Robert Jones | 4 | 3 |
| Susan Jones | 3 | 1 |
+ --------- + ---------------- + ------------- +
3 rows

If you compare the two results, Robert Jones is included twice in the result.

If anybody knows of a way, please let me know.

--Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-01 : 19:20:02

I'm surprised the query returns anything, as a.name is in your select, but not your group by, and your group by has b.numpages, which will add another row if robert jones is associated with 2 different b.numpages. Are you just trying to count the number of books an author has written since 1960 that have more than 1000 pages?

Jim

Everyday I learn something that somebody else already knew

juan11041970
Starting Member

2 Posts

Posted - 2011-08-01 : 21:59:42

Yes that is what I am trying to do. Total number of books since 1960 and also the total number with more than 1000 pages.

In other words, two counts are needed. Total number of books and total number of books with more than 1000 pages.

--Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 00:52:07

[code]
select a.name,
count(case when b.year_written >= 1960 then 1 else null end) as TOTAL_BOOKS,
count(case when b.numpages > 1000 then 1 else null end) as PROLIFIC
from test.authors a
join test.books b
on a.idauthors = b.author_id
group by a.name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources