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 2008 Forums
 Transact-SQL (2008)
 Get COUNT of joined rows?

Author  Topic 

malachi151
Posting Yak Master

152 Posts

Posted - 2011-11-02 : 18:59:21
I'm doing a query with several LEFT JOINs and I want to get the total count of all records, plus the count of the records where each join is successful.

Ideas?

Thanks

malachi151
Posting Yak Master

152 Posts

Posted - 2011-11-02 : 19:07:51
Well I've since come up with one solution, but curious to know if there is a better one. I have a primary key on the primary table, so I'm doing:

SELECT COUNT(*), COUNT(DISTINCT CAST(pk_col as varchar)+CAST(join_col as varchar)), etc., etc.


In this case where the joined table returns no results the join_col is NULL, so that NULLs out the concatenation, which is then eliminated from the aggregate.

If there way more appropriate than this though?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 20:15:47
quote:
get the total count of all records

COUNT(*)

quote:
count of the records where each join is successful.

COUNT ( <column name of table in LEFT JOIN> )



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:28:50
quote:
Originally posted by malachi151

Well I've since come up with one solution, but curious to know if there is a better one. I have a primary key on the primary table, so I'm doing:

SELECT COUNT(*), COUNT(DISTINCT CAST(pk_col as varchar)+CAST(join_col as varchar)), etc., etc.


In this case where the joined table returns no results the join_col is NULL, so that NULLs out the concatenation, which is then eliminated from the aggregate.

If there way more appropriate than this though?


is it one to many relation? why should you need DISTINCT inside count?

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

Go to Top of Page

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-03 : 05:12:05
try this,

select COUNT(*), COUNT ( leftjoincolumnname ) from table1 left join latble2 on table1.leftjoincolumnname = table2.leftjoincolumnname

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 05:22:53
quote:
Originally posted by jassi.singh

try this,

select COUNT(*), COUNT ( leftjoincolumnname ) from table1 left join latble2 on table1.leftjoincolumnname = table2.leftjoincolumnname

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh


is it a xerox bot?

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

Go to Top of Page

malachi151
Posting Yak Master

152 Posts

Posted - 2011-11-03 : 10:59:45
None of these other suggestions work, because there are duplicates in the joined table, i.e. records in the joining tables join to multiple records in the primary table. Also, though I didn't mention it, in reality the primary keys on both tables are multi-column keys...

But yeah, I didn't need the DISTINCT in there...
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-03 : 12:10:37
If those suggestions are not working then we need YOU to provide more informaiton so we aren't guessing. Here is a link that shows you how to prepare DDL, DML and expected output so we can provide you better assistance:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
   

- Advertisement -