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.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2010-03-24 : 17:00:31
|
I have 2 views:v_SMSAppdata -- A detail table with many of each AppIDsv_CommonApp -- A lookup table with AppName and AppIDAll I want to do is get a count of each one. I know I can use a group by but I heard in some cases a subquery would be faster and I wanted to learn how to do them. Here is what I have so far:SELECT v_CommonApp.AppName , (SELECT count(v_SMSAppData.AppID) FROM v_SMSAppData -- LEFT JOIN v_CommonApp ON v_SMSAppData.AppID = SA.AppID) as CountofApps WHERE v_SMSAppData.AppID = v_CommonApp.AppID) as CountofAppsFROM v_CommonAppLEFT JOIN v_SMSAppData SA ON v_CommonApp.appID = SA.AppID ORDER BY v_CommonApp.AppName Right now I get a true count, but each AppID is repeated for each detail record, so I will get 1000 lines of 'Excel' with countofApps=1000Then, I ran it with the above commented out JOIN and commented out the WHERE clause and it has been running for 15 minutes now and just beginning to return results in absurd huge counts.So, that didn't work. I have tried RIGHT JOINS, INNER JOINS, and many other things and still can't get it.I thought I had a basic understanding of this stuff, but it looks like I still just don't get it. This is no going on at least 2 hours. All I want is 2 columns (a total of about 200 rows) with a count of each line. There are about 3,000,000 detail records so the counts should total about that.I also don't want to use a DISTINCT because I know that is just hiding my ignorance. I just want to know how to do it for once and for all.Thank you for any help.Duane |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-03-24 : 19:37:29
|
| SELECT v_CommonApp.AppName count(v_SMSAppData.AppID) as CountofAppsFROM v_CommonAppLEFT JOIN v_SMSAppData SA ON v_CommonApp.appID = SA.AppID GROUP BY v_CommonApp.AppNameORDER BY v_CommonApp.AppName=======================================There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980) |
 |
|
|
|
|
|
Simple subquery