2nd Count column - 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

2nd Count column

Author

Topic

Anubis
Starting Member

3 Posts

Posted - 2007-10-29 : 07:21:26

Hey there,

I am new to SQL so please bear with me. I was wondering how to create a new column that counts the EntityId

COUNT(CdsQuotes.EntityId)

and puts it into a seperate column.

What I am trying to achieve is a count of entity IDs from one table and a count of entity IDs from another table - but the 2nd count must be from the first counts results (hope this makes sense).

So it returns a count from table 1 and then a corresponding count from table 2 (based on table 1 entityIDs)

Any ideas?

SELECT 

Entity.EntityName,
SuspectBond.EntityId, 
COUNT(SuspectBond.EntityId) as "Suspect Bonds" 

FROM SuspectBond

INNER JOIN Entity ON 
SuspectBond.EntityID = Entity.EntityId

WHERE 
SuspectBond.StatusId = 1 

GROUP BY 
SuspectBond.EntityId, 
Entity.EntityName

ORDER BY 
COUNT(SuspectBond.EntityId) DESC

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 07:30:18

"Any ideas?"

Not from the way you have described it No - sorry!

You have two tables:


FROM SuspectBond
    INNER JOIN Entity
        ON SuspectBond.EntityID = Entity.EntityId

and you are Grouping by SuspectBond.EntityId, Entity.EntityName

So any COUNT is going to show you the number of rows for a given SuspectBond.EntityId / Entity.EntityName combination.

If there might be some rows in Entity which have no rows in SuspectBond (or vice-versa) the you could use an OUTER JOIN and then you could get a different COUNT for each table ...

Otherwise have another go at describing the problem pls

Kristen

Anubis
Starting Member

3 Posts

Posted - 2007-10-29 : 07:35:12

Hmmm...so there is no way of getting a second count from another table from the first counts results?!

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 08:29:01

Yes, there are several ways. But I would need to understand what it is that you want in order to help you with that.

Kristen

Anubis
Starting Member

3 Posts

Posted - 2007-10-29 : 09:16:56

Hi Kristen,

Basically the code needs to:

> Match entityID to entityname using a join (table 1 - this is completed)
> Match entityID to the count of suspect quote records (table 2 - this is completed)
> Then for entityID listed return another count from table 3.

I.E:

EntityName | EntityID | Suspect Quotes | Another Count

=============================================================
EntityName = Table1 join (this works)
EntityId = comes from original table with syntax
SuspectQuotes = comes from original table with syntax
Another Count = Table2 join (this doesn't)

So...

EntityName | EntityID | Suspect Quotes | Another Count
NameA | 123 | 32 | 12
NameB | 234 | 30 | 15

and so on...

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 09:59:41

"Then for entityID listed return another count from table 3."

Well, this isn't particularly efficient, but it will do if the number of rows Selected is not too large - more than a few 100 ought to be tackled using something more complex


SELECT Entity.EntityName,
	SuspectBond.EntityId, 
	COUNT(SuspectBond.EntityId) as [Suspect Bonds],
	(SELECT COUNT(*) FROM Table3 AS T3 WHERE T3.entityID = SuspectBond.EntityID) AS [Another Count]
FROM ...

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 10:02:03

Actually that might give an error about "sub-selects in aggregates" in which case you would need:


SELECT EntityName,
	EntityId, 
	[Suspect Bonds],
	(SELECT COUNT(*) FROM Table3 AS T3 WHERE T3.entityID = EntityID) AS [Another Count]
FROM
(
... your original query here (WITHOUT the Order By clause) ...
) AS X
ORDER BY [Suspect Bonds] DESC

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources