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 |
|
bigblue
Starting Member
1 Post |
 Posted - 2004-01-04 : 13:27:18
|
| I'd like to create some aggregate statistics based on data from two tables: In the first table, I have member organizations. In the second table, I have zero-to-many records with data about the members, with one record for each year of data. Some organizations may not have reported any data, in which case there will be no related records found. Others will have one or more years of reporting, with a seperate record for each year. It includes things like total new members (for the year) total membership, and annual revenue. I'd like to create a brief report that that takes a group of organizations (for a given state or zip code, for example) and calculates the average values for those organizations that have reported for a given year. For California in the year 2002, there might be 1,000 organizations, but only 300 reported any data (and therefore have a 2002 record in the second table). Any suggestions for how I'd go about doing this elegantly? I'm figuring I'll grab a total count of records, a count of records with non-null values, and then loop through the recordset to do the math. Sound about right? The part I'm not too sure about is relating the two tables. Basically I need to grab the stats from table #2 where the memberID is in a given state (which is in table #1). Jeff HesterInstant Messaging | Avatars | Website Templates |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-01-04 : 19:58:12
|
| Why not just use the AVG() function? Something likeSELECT organization, avg(mystatfield)FROM table2 JOIN table1 on table2.ForeignKey = table1.PrimaryKeyWHERE table2.StateCode = 'CA'GROUP BY organization--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
|
|
|
|
|
|
Aggregate data - Newbie question