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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-09 : 14:37:40
|
| Marcus Cheatham writes "As a web developer serving the health industry, I create applications that enable users create custom database queries. One of the most popular things users want to do is get a frequency table showing the relationship between two variables, with row and column percents in the cells, as well as a complete set of marginal percentages. For example, suppose I want to know the relationship between sex and smoking. You'ld have a two-by-two table with sex across the top and whether or not one is a smoker down the side. In each cell you could look at the row and column percentages (ie: percent of women who smoke, percent of smokers who are women, etc. etc.). I can do this, but I am curious about what the state-of-the-art is. Am I using best practices? First, assume that using a PIVOT command is out. What I do is query the database four times getting four recordsets: once for cell counts (COUNT GROUP BY row variable AND column variable), once for row counts (COUNT GROUP BY row variable), once for column counts (COUNT GROUP BY column variable) and finally to get an overall record count. You can use these recordsets to get the marginal percentages also. To create the HTML table I iterate through these recordsets throwing the cell count, the row percent (cell count divided by row count), and the column percent (cell count divided by the column count) into each cell as the HTML table is created. For marginals I'd throw up the row or column count, and for the percentage, divide the row or column count by the total count. This process is rather slow with large databases, requires at least 4 data base connections, and the programming gets rather hairy if you want to enable users to make multilayered tables, deal with missing data, etc. So is there a better way?" |
|
|
|
|
|
|
|
Frequency table with row and column percents