Counting values from table and associating them with another

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 2000 Forums

Transact-SQL (2000)

Counting values from table and associating them with another

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-12 : 07:46:46

Per writes "Hi SQL-Team'ers - and a happy New Year to you... blurp ;-)
I have 2 tables (Company and News)

Company-table:


CompanyID    CompanyName   CompanyPhone
--------------------------------------
1            XYZ Company   555-1234
2            Test & Co.    555-0222
3            MyCompany     555-4741
4            Incorp. Inc.  555-5541

News-table:


NewsID   CompanyID   Subject      Body
--------------------------------------
1        2           bla..bla...  bla..bla...
2        2           bla..bla...  yada..
3        2           bla..bla...  yada..
4        3           bla..bla...  yada, yada.
5        1           bla..bla...  yada, yada.
6        1           bla..bla...  yada, yada.
7        1           bla..bla...  yada, yada.
8        1           bla..bla...  yada, yada.

I want to count the number of News each Company is associated with.
And if a Company does not have a News associated the output must write 0 as Number Of news.
E.g.:


CompanyName     Number of News    CompanyPhone
----------------------------------------------
XYZ Company     4                 555-1234
Test & Co.      3                 555-0222
MyCompany       1                 555-4741
Incorp. Inc.    0                 555-5541

Could you provide some SQL that solves this task - I'm sure I'm not the only one having this problem?"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-01-12 : 08:58:56

What is this for?

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-12 : 12:41:09

SELECT Company.CompanyName, (SELECT COUNT * WHERE News.CompanyID = Company.CompanyID FROM News) AS NumberofNews, Company.CompanyPhone FROM Company ORDER BY 1

HTH

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 13:22:53

SELECT c.CompanyName, Count(*) AS NewArticles, c.CompanyPhone
FROM Company c INNER JOIN News n
ON c.CompanyId = n.CompanyId

Brett

8-)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-12 : 13:48:04

Sorry in advance for stupid questions.

So COUNT(*) will be based on the INNER JOIN results? Shouldn't there be a GROUP BY c.CompanyName here somewhere?

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 14:13:57

quote:
Originally posted by Ken Blum

Sorry in advance for stupid questions.

So COUNT(*) will be based on the INNER JOIN results? Shouldn't there be a GROUP BY c.CompanyName here somewhere?

Hey...actually...another bone head post

[code]
SELECT c.CompanyName, Count(*) AS NewArticles, c.CompanyPhone
FROM Company c INNER JOIN News n
ON c.CompanyId = n.CompanyId
GROUP BY c.CompanyName, c.CompanyPhone
[code]

Sorry...

Brett

8-)

EDIT: Yes the count will be based on the result of the join...which I'm assuming this is a 1 to many (parent child).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources