• Count(x=a)/count(x=b) vs (select count() where

    From vjp2.at@at.BioStrategist.dot.dot.co@21:1/5 to All on Thu Jan 9 19:46:29 2020
    select d , (select count() where x=a)/(select count() where x=b) group by d

    ??


    Do I have to spell out the full select or does it get it from the one it is nested in?


    - = -
    Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus
    blog: panix.com/~vjp2/ruminatn.htm - = - web: panix.com/~vjp2/vasos.htm
    facebook.com/vasjpan2 - linkedin.com/in/vasjpan02 - biostrategist.com
    ---{Nothing herein constitutes advice. Everything fully disclaimed.}---

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to vjp2.at@at.BioStrategist.dot.dot.co on Thu Jan 9 21:51:49 2020
    On 09/01/2020 20:46, vjp2.at@at.BioStrategist.dot.dot.com wrote:
    select d , (select count() where x=a)/(select count() where x=b) group by d

    ??


    Do I have to spell out the full select or does it get it from the one it is nested in?

    select d, sum(case when x = a then 1 else 0 end case)/sum(sum(case when
    x = b then 1 else 0 end case) from t group by d

    ??

    For nested queries that depends on values from the data will need to
    have a reference to the original table like t.a (assuming the table is
    called t or aliased as t and has a column a), the nested select will
    still need to be a full select from the table.

    Just keep in mind that the nested select will be executed multiple times
    and can slow down your query a lot, you may gain speed in avoiding to do
    a nested select, specially badly indexed tables with many rows.

    Use the explain statement in mysql/mariadb/... to see how the different
    types of queries affects fetching the data.

    --

    //Aho

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From vjp2.at@at.BioStrategist.dot.dot.co@21:1/5 to All on Fri Jan 10 10:32:55 2020
    thanks


    - = -
    Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus
    blog: panix.com/~vjp2/ruminatn.htm - = - web: panix.com/~vjp2/vasos.htm
    facebook.com/vasjpan2 - linkedin.com/in/vasjpan02 - biostrategist.com
    ---{Nothing herein constitutes advice. Everything fully disclaimed.}---

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)