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? |
|
|
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] |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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.leftjoincolumnnamePlease mark answer as accepted if it helped you.Thanks,Jassi Singh |
|
|
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.leftjoincolumnnamePlease mark answer as accepted if it helped you.Thanks,Jassi Singh
is it a xerox bot? ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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... |
|
|
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 |
|
|
|