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 |
|
J K
Starting Member
7 Posts |
 Posted - 2002-03-12 : 10:55:12
|
Hi there, I am a bit stuck with a series of statistics I need to produce from my SQL table...I could do each one as a seperate recordset query in my ASP page, but that seems rather cumbersome, and I'm sure there is a nicer way to do it using some combination of aggregate functions in one (or maybe two!) SELECT statement. Sorry in advance that this is a long one...!Firstly, the table is called tblFeedback, and contains fields like First_Impressions (int), More_news (bit) and Area (int)Here are the three things I need to do (all quite similar)...The First_Impressions field contains a value between 1 and 5 (from 'great' to 'poor'). In this instance, I want to find out what the most popular/frequent number is in this column to tell me a modal average. Let's say most people voted a 3. From this I could then find out the percentage of people who then voted 3, but I need to find out what the most popular response was first.The More_news column is a different kettle of fish. I have a series of columns in the table like More_news (More_links, More_FAQs) so first I need to find out which of the 3 columns was most popular - ie, which 'More' column did people tick most. So I need somehow to compare a sum of responses in each of those fields to see which is the highest, and then do the same percentage thing. This will tell me what people have requested most, and what percentage of the voters this was. I would then like to show the next most popular, and it's percentage.The Area column holds a number representing an area in a seperate lookup table. I would like to know where the most popular area was that people chose (ie, which number appears in that column the most).How can I do these things in one query? Is it possible? Or do I need a seperate recordset for each thing? Please help! Regards,Julia |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-12 : 18:10:36
|
JK - I'll try to help, but I'm not a statistics guru. (I think I failed it at uni - well I got a credit but it was scaled up big-time  ).I agree with your intent. You'll need to do this as an SP (stored procedure) though - which returns all your results. It should be roughly 1 billion times faster than doing it in your ASP page (only roughly, mind).Firt_Impressions - mode - I've done it all the hard way - but someone else might know better....declare @tot_freq floatselect @tot_freq = count(*) from tblFeedbackselect First_Impressions, freq, ((freq/@tot_freq)*100) as percfrom (select First_Impressions, count(First_Impressions) as freqfrom tblFeedbackgroup by First_Impressions) fdorder by freq descThis returns something like:First_ freq percentage3 19 59.3754 6 18.752 4 12.51 2 6.255 1 3.125I'll try and post back the other bits later on.cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
J K
Starting Member
7 Posts |
Posted - 2002-03-13 : 06:28:32
|
Excellent, works a treat, but I have just changed the @total to be the total people that voted for that question (otherwise it doesn't aggregate the null values). So I am counting the people who voted for that question, rather than overall voters, as I think that's a fairer statistic (mind you, as we have all gathered by now, stats is not my strong point! )That's been a great help - I will adapt this modal thing for the area bit, but still a bit stuck on comparing say, 5 columns to see which of them is most popular and then working out percentages...but I'm going to have a go...Many thanks for your help!Julia |
|
|
|
jns
Starting Member
19 Posts |
Posted - 2002-03-13 : 07:48:07
|
| For statistical purposes if I have a series of five potential answers with the ability for a person not to answer, in this case your NULL value. You really have six potential options and should calculate based upon the NULL value as the person still made a choice. This will keep your statistics true and not scaled.As for comparing your five columns, I'd consider dumping the data into a #temp table of three columns, an unique identifier, column_name, column_value and merge all the data together. From there you can easily join, compare and report on the data. There maybe a better/faster way but this has worked for me on countless occasions. |
|
|
|
J K
Starting Member
7 Posts |
Posted - 2002-03-13 : 07:53:07
|
| Thanks - yes, I suppose you're right about not scaling stats...perhaps I'll change it back.As for the temp table idea, I think that could be a goer...do you think this will give better performance than creating some sort of view that is there permanently that I can select from? I could do the calculations in my ASP, rather than in the db for that...oh well - food for thought! I'll have a go with what you suggest.Many thanks,Julia |
|
|
|
|
|
|
|
|
statistics headache...