SQL help (yes... again)

[link9]

Sick of my SQL questions yet?

Ok, one table(actually it's a view), 10 columns, 10 rows

columns are named id, Q1, Q2, ... Q9

values in the fields are 1 - 10 for Qn fields, and a five digit idNumber for the id field

The view is already filtered from a bigger table so that any entry in the view has a value of 10 for Q1

What I now want to do it to pick out the best entry of the view, and here's how I should figure it:

Average together all the values from the other 8 columns, and whatever value is the highest, that is the one that I want, and I'll be needing the id -- so that I can match it back to the corresponding account from another table.

I'm thinking some combination of a MAX and an AVG function... but that's why I'm here... I'm not sure, and have been floundering on it for a few too many minutes. ;-)

And thanks for any help (as always!)

Paul Prewett

 

[link9]

Thinking about it further, it would be sufficient if I could just pick out the MAX of the SUM of all the fields on one row --

in pseudocode:
MAX(SUM(Q1 + Q2 + Q3... Q9))

Would that be easier?

 

[link9]

Okay then --

Found a solution, but now would like to put it all into one nice, neat, query...

Here is the SELECT that makes the first view (with the name, franchiseQ1)--

SELECT mhrsData.[id],
SUM(Q1 + Q2 + Q3 + Q4 + Q5 + Q6 + Q7 + Q8 + Q9)
AS total
FROM mhrsData
WHERE (Q1 = 10)
GROUP BY [id]

and here's the one that picks out my Best In Class from that view:

SELECT MAX(total) AS total, MAX([id]) as bic
FROM franchiseQ1

I tried to just put the whole first query in parentheses where 'franchiseQ1' shows up the the second query, but of course, that didn't work --

How would I combine these two queries?

Thanks
paul

 

[Andel]

Hi, can you provide a sample table/data and a desired result of your query?
Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

Try this one.

SELECT total, id as bic
FROM franchiseQ1
WHERE total=(Select max(total) FROM franchiseQ1)
Terry

http://members.home.net/tlbroadbent/index.htm