calculating percentage

  • Hello Guys!

    following...

    I have a query

    SELECT Motivo.motivo, Count(Motivo.motivo) AS ContarDemotivo

    FROM Motivo

    GROUP BY Motivo.motivo

    ORDER BY Motivo.motivo DESC , Count(Motivo.motivo) DESC;

    How Can I claculat the percentage?

    I wish put a column with percentage of total.

    any tip!

    tks!

  • percentage....of what? against what domain? Help us help you - give us some details we can work with...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • For example I have a colmn with total of "Motivo", group by "Motivo".

    Now I need add a colmn that calculat the percentage.

    how ?

    Percentage = Count(Motivo) group by "Motivo"

    SUM(Motivo), but this value is not group by "Motivo", is total absoluto.

  • Guys!

    Some tip ? 😀

  • If you're writing this in MSAccess' version of SQL - you may need multiple queries (I don't recall it allowing for sub-queries).

    Totals: select count(*) Grandtotal from motivo

    SELECT Motivo.motivo, Count(Motivo.motivo) AS ContarDemotivo,Count(Motivo.motivo)/Totals.Grandtotal as pct

    FROM Motivo cross join Totals

    GROUP BY Motivo.motivo

    ORDER BY Motivo.motivo DESC;

    In SQL 2000 you could write it using derived tables:

    SELECT Motivo.motivo,

    Count(Motivo.motivo) AS ContarDemotivo,

    Count(Motivo.motivo)/Totals.Grandtotal as pct

    FROM Motivo

    cross join

    (select count(*) Grandtotal from motivo) Totals

    GROUP BY Motivo.motivo

    ORDER BY Motivo.motivo DESC ;

    In SQL 2005 you can do it with just one query and some windowed counts:

    SELECT distinct Motivo.motivo,

    Count(Motivo.motivo) OVER (partition by motivo.motivo) AS ContarDemotivo,

    Count(Motivo.motivo)/(sum(motivo.motivo) OVER ()) as pct

    FROM Motivo

    ORDER BY Motivo.motivo DESC ;

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply