ORDERing BY # of Conditions Satisfied

# Thread: ORDERing BY # of Conditions Satisfied

1. mark Guest

## ORDERing BY # of Conditions Satisfied

SUM(), AVG(), COUNT (*), etc., only work on columns.<BR><BR>How do I get the results of a multiple-condition SQL query ORDERed BY the percentage (or number) of conditions satisfied?<BR><BR>E.g.:<BR><BR>SELECT * FROM Table<BR>WHERE ConditionA = true AND ConditionB = true AND ConditionC = true<BR>ORDER BY [percent true] DESC<BR><BR>I.e.:<BR><BR>if 3 of 3 conditions are true, then 100%<BR>if 2 of 3 conditions are true, then 75%<BR>if 1 of 3 condition is true, then 25%,<BR>and if 0 of 3 conditions is true, then 0% ?<BR><BR>or:<BR><BR>% true = # conditions true / # conditions<BR><BR>Do I have to use multiple queries and Recordsets?<BR><BR>TIA

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Access??? (And math is faulty)

&nbsp;<BR>First of all, if 1 of 3 is correct, the percentage is 33.3333%.<BR>If 2 or 3, then 66.66667%. *NOT* 25% and 75%. <BR><BR>Anyway...<BR><BR>If you are using Access, <BR><BR>SELECT Table1.item, A, B, C, -100 * ( CInt(A) + CInt(B) + Cint(C) ) / 3 AS PercentCorrect<BR>FROM Table1;<BR><BR>The trick: Converting a Yes/No (boolean) field to an integer in Access converts a false to zero and a true to -1. So if you convert each boolean to a number and add the numbers, you will get an answer in the range of 0 to -3. We divide that by 3 and get 0.00 or -0.33 or -0.67 or -1.0. Then we multiply that by -100 and get a percentage: 0 or 33.3333 or 66.6667 or 100. <BR><BR>

3. DUBYAH Guest

## RE: billy-boy "anser" is WRONG WRONG WRONG!!!!

an just who who ELECTED you P resdent??? ooh billy-boys know high skool algebra. ooh little turd nerd.<BR><BR><BR>give the man a break, billy-boy! <BR>he woorks for ME!!! hoo cares bout reedin ritin n rithmetic anysways? i dindnt!<BR><BR>best stop harassing people heer. were all sick an tired of yer ROOD + UNCIVIL BEHAVYER HERE!!!!!<BR><BR>or else i&#039;ll sick Dick Chainy on ya!<BR><BR>aight?!<BR><BR>DUBYAH<BR><BR>ps yer "anser" is WRONG!!!!!

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Then why did it work for me?

I really actually honestly wrote a test page. And it worked. Perfectly.<BR><BR>Now...what part are you claiming is wrong?<BR><BR>And who said I was president? I wouldn&#039;t put up with such a demotion.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Mark: You really work for this dork?

If so, I truly feel sorry for you.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Yeah, yeah...a typo...

I wrote:<BR>&nbsp; &nbsp; &nbsp; If 2 or 3, then 66.66667%. <BR><BR>The "or" in that should be "of".<BR><BR>If this is what dorky means by my "anser" being wrong...well, sue me.<BR><BR>Oh...and I didn&#039;t put in the ORDER BY clause. Because there&#039;s 2 or 3 ways to do it. Ask if you care.<BR><BR><BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
226

## Get a live

Why can&#039;t we just work togheter instead of yelling names? Please, get a live and stop posting msg like this when somebody tries to help yes?<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•