Thanks for testing & reporting back. Nice to know my brainisnot totally rotted out yet ;-} On Feb 16, 2015 4:41 AM, "Bart Smissaert" <bart.smissaert at gmail.com> wrote:
> Yes, both your suggestions work indeed and thank for that. > I prefer the second one as it looks a bit neater and makes it more clear > what is going on. > > RBS > > On Mon, Feb 16, 2015 at 10:23 AM, John McKown < > john.archie.mckown at gmail.com> > wrote: > > > On Mon, Feb 16, 2015 at 3:53 AM, Bart Smissaert < > bart.smissaert at gmail.com> > > wrote: > > > > > Yes, I realise that, but is there any way to get this sorted with that > 47 > > > row at the bottom? > > > > > > > ?Please forgive me if I make a mistake. It is 04:20 and I can't sleep. > If? > > the problem is as Mr. Gunter said, then you might want to try one of the > > following. > > > > SELECT DS.DRUG_NAME AS DRUG_NAME, > > SUM(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) AS SENSITIVE, > > SUM(INSTR(M.ASSOCIATED_TEXT, DS.RESISTANT_STRING) > 0) AS RESISTANT, > > COALESCE(ROUND(TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > > 0) > > / > > (TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) + > > TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.RESISTANT_STRING) > 0)), 2),0) > AS > > RATIO > > FROM DRUG_SENSITIVITY_STRINGS AS DS > > JOIN MSU AS M > > WHERE M.ASSOCIATED_TEXT LIKE '%Nitrofurantoin..... R%' > > GROUP BY DS.DRUG_NAME > > ORDER BY RATIO DESC, RESISTANT ASC > > ?-- Make RATIO come out 0 by using COALESCE if it would otherwise be NULL > > > > or maybe > > > > SELECT DS.DRUG_NAME AS DRUG_NAME, > > SUM(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) AS SENSITIVE, > > SUM(INSTR(M.ASSOCIATED_TEXT, DS.RESISTANT_STRING) > 0) AS RESISTANT, > > ROUND(TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) / > > (TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) + > > TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.RESISTANT_STRING) > 0)), 2) AS > > RATIO > > FROM DRUG_SENSITIVITY_STRINGS AS DS > > JOIN MSU AS M > > WHERE M.ASSOCIATED_TEXT LIKE '%Nitrofurantoin..... R%' > > GROUP BY DS.DRUG_NAME > > ORDER BY COALESCE(RATIO,0) DESC, RESISTANT ASC? > > ?-- If RATIO is NULL, then sort it as if it were ZERO instead.? > > > > > > > > > > > > RBS > > > > > > On Mon, Feb 16, 2015 at 9:47 AM, Hick Gunter <hick at scigames.at> wrote: > > > > > > > You are attempting to compute 0/0 which is NULL and happens to be > > smaller > > > > than 0/47 which is 0. > > > > > > > > -----Urspr?ngliche Nachricht----- > > > > Von: Bart Smissaert [mailto:bart.smissaert at gmail.com] > > > > Gesendet: Montag, 16. Februar 2015 09:49 > > > > An: General Discussion of SQLite Database > > > > Betreff: Re: [sqlite] Can this be sorted? > > > > > > > > Sorry, it looked OK on my side, but I suppose some of the Excel > > > formatting > > > > got through and messed things up. > > > > > > > > Simplified it will be: > > > > > > > > field1 field2 field 3 > > > > ------------------------------------------- > > > > 0 47 0 > > > > 0 0 0 > > > > 0 0 0 > > > > > > > > and do: > > > > > > > > order by field3 desc, field2 asc > > > > > > > > Trying to get the row with 47 at the bottom. > > > > > > > > > > > > RBS > > > > > > > > On Mon, Feb 16, 2015 at 2:58 AM, Igor Tandetnik <igor at tandetnik.org> > > > > wrote: > > > > > > > > > On 2/15/2015 6:54 PM, Bart Smissaert wrote: > > > > > > > > > >> Result is shown below. > > > > >> > > > > >> > > > > >> *Drug**Sensitive* *Resistant**Ratio*Ertapenem > > > > >> 10201Meropenem301Pip/Tazobactam301 > > > > >> Cefalexin/Cefdrxl4070.85Gentamicin310.75CiprofloxacinS1570. > > > > >> 68Amoxicillin2115 > > > > >> 0.58Ampicillin760.54Trimethoprim18290.38Amoxyclavulanate6130.32 > > > > >> Nitrofurantoin0470Cefixime000Erythromycin000Fluclox(Met/Ox) > > > > >> 000Penicillin000 > > > > >> Tetracycline00 > > > > >> > > > > > > > > > > This is illegible. > > > > > > > > > > _______________________________________________ > > > > > sqlite-users mailing list > > > > > sqlite-users at mailinglists.sqlite.org > > > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > sqlite-users at mailinglists.sqlite.org > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > ___________________________________________ > > > > Gunter Hick > > > > Software Engineer > > > > Scientific Games International GmbH > > > > FN 157284 a, HG Wien > > > > Klitschgasse 2-4, A-1130 Vienna, Austria > > > > Tel: +43 1 80100 0 > > > > E-Mail: hick at scigames.at > > > > > > > > This communication (including any attachments) is intended for the > use > > of > > > > the intended recipient(s) only and may contain information that is > > > > confidential, privileged or legally protected. Any unauthorized use > or > > > > dissemination of this communication is strictly prohibited. If you > have > > > > received this communication in error, please immediately notify the > > > sender > > > > by return e-mail message and delete all copies of the original > > > > communication. Thank you for your cooperation. > > > > > > > > > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > sqlite-users at mailinglists.sqlite.org > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > He's about as useful as a wax frying pan. > > > > 10 to the 12th power microphones = 1 Megaphone > > > > Maranatha! <>< > > John McKown > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
