query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

tommich
Starting Member

4 Posts

Posted - 2012-01-07 : 16:55:27

I have a problem with sql;
i have 2 tables;boeken(boek_id,titel,cat_id,aut_id),categorie(cat_id,categorie)
i cannot find a solution for this problem:
show the average number of books per categorie.(the result is one number,average there are x books in a categorie)

i tried this:


select CATEGORIE,COUNT(*)
from CATEGORIE c,BOEKEN b
where c.cat_id=b.cat_id
group by categorie

Now i have 3 numbers,because there are 3 categories,how can i make an average from these 3 results?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 16:59:26

You can make your current query into a subquery and then get the average as in:

SELECT AVG(n) FROM
(
   select CATEGORIE,COUNT(*) AS N
   from CATEGORIE c,BOEKEN b
   where c.cat_id=b.cat_id
   group by categorie
) s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 02:32:40

if you want decimal result


SELECT AVG(n*1.0) FROM
(
   select CATEGORIE,COUNT(*) AS N
   from CATEGORIE c,BOEKEN b
   where c.cat_id=b.cat_id
   group by categorie
) s

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-08 : 07:31:44

Thanks visakh!

More than likely, you do want to have decimal result unless you have hundreds or thousands of books in each category, that you don't care about fractional part of the average.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 07:42:12

No problem Sunitha

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 07:43:33

for benefit of OP, the reason for losing decimal part in first query is this

http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

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

tommich
Starting Member

4 Posts

Posted - 2012-01-08 : 17:00:15

It works,

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources