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 >
