Trying to count... - 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)

Trying to count...

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-08-24 : 04:57:56

I'm trying to count, but I'm no SQL expert, so if anyone can help...

This is what I got:

SELECT
[ConferenceID],
[ConferenceCountry],
[ConferenceName],
[ConferenceCity],
[ConferenceYear],
[NumberOfAbstracts] = (select count(*) from Abstracts where ConferenceID = [ConferenceID])
FROM
dbo.AbstractsConferences
ORDER BY
[ConferenceYear]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-24 : 05:00:41

Hi, Tru this once

select * from AbstractsConferences ac
left join (select ConferenceID , count(*) from AbstractsConferences Group By ConferenceID ) as acc on acc.ConferenceID = ac.ConferenceID

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-08-24 : 05:07:50

I did this:

SELECT
ac.[ConferenceID],
ac.[ConferenceCountry],
ac.[ConferenceName],
ac.[ConferenceCity],
ac.[ConferenceYear],
acc.[NumberOfAbstracts]
FROM
dbo.AbstractsConferences ac
left join (select ConferenceID, count(*) as [NumberOfAbstracts] from Abstracts Group By ConferenceID) as acc on acc.ConferenceID = ac.ConferenceID
ORDER BY
[ConferenceYear]

This works!!! only, it returns -1 for conferences without an abstract.
So can I also make it return a 0 then?

The secret to creativity is knowing how to hide your sources. (Einstein)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-24 : 05:15:00

quote:

Originally posted by trouble2

SELECT 
	[ConferenceID],
	[ConferenceCountry],
	[ConferenceName],
	[ConferenceCity],
	[ConferenceYear],
	[NumberOfAbstracts] = COALESCE((select count(*) from  Abstracts where ConferenceID = [ConferenceID]), 0)
FROM	
	dbo.AbstractsConferences
ORDER BY
	[ConferenceYear]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-24 : 05:17:03

[code]SELECT ac.[ConferenceID],
ac.[ConferenceCountry],
ac.[ConferenceName],
ac.[ConferenceCity],
ac.[ConferenceYear],
COALESCE(acc.[NumberOfAbstracts], 0) AS [NumberOfAbstracts]
FROM dbo.AbstractsConferences AS ac
LEFT JOIN (
SELECT ConferenceID,
COUNT(*) AS [NumberOfAbstracts]
FROM Abstracts
GROUP BY ConferenceID
) AS acc ON acc.ConferenceID = ac.ConferenceID
ORDER BY [ConferenceYear][/code]

N 56°04'39.26"
E 12°55'05.63"

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-08-24 : 05:24:23

Right, thanks, that worked fine...

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources