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
