case when with count ms sql

  • how can I achieve this result below?

    | id  | id_status | rate    |

    | --- | ----------- | ------  |

    | 25 | X               |62.5% |

    | 15  | Y               |37.5% |

    having tried this

    SELECT 
    COUNT(tab.id) AS id,
    tab.status AS id_status,
    (CASE
    WHEN tab.status = 'X' THEN (25/40) * 100 -- this is where I'm stucked (40 = total of ids)
    WHEN tab.status = 'Y' THEN 100 - ((25/40) * 100)
    END AS rate
    FROM table AS tab
    WHERE tab.status in ('X', 'Y')

     

  • Welcome to the forum.

    You need to tell us what the source data looks like in order for us to tell you how to achieve the result.

    Alternatively, be more specific with your question. It's difficult to tell exactly what the issue is at the moment.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Are you trying to get something like this? (Untested and probably has error!)

    SELECT pcx = SUM(IIF(tab.status = 'X', 1, 0)) * 100.0 / COUNT(1)
    ,pcy = SUM(IIF(tab.status = 'Y', 1, 0)) * 100.0 / COUNT(1)
    FROM tbl tab
    WHERE tab.status IN ( 'X', 'Y' );

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks !

    I have a table with 'statues' column. I can have the total number of each statut(x,y,z) with count. (MS SQL)


    |status| count(status) |
    |x | 2 |
    |y | 3 |
    |z | 5 |

    What I'm looking for is the percentage occurrence of each status


    | statut| number of statut | rate(%) |
    |x | 2 | 20 |
    |y | 3 | 30 |
    |z | 5 | 50 |

    with

    rate(%) x = (2/(2+3+5))*100

    rate(%) y = (3/(2+3+5))*100

    rate(%) z = (5/(2+3+5))*100

    SELECT tab.status                 as status,
    COUNT(tab.status) as total_nber_of_status

    FROM table as tab
    GROUP BY tab.status
  • Here is one way. (For future reference, please note how I set up sample data in a form which can be cut & pasted directly into SSMS.)

    DROP TABLE IF EXISTS #t;

    CREATE TABLE #t
    (
    Stat CHAR(1)
    ,ct INT
    );

    INSERT #t
    (
    Stat
    ,ct
    )
    VALUES
    ('x', 2)
    ,('y', 3)
    ,('z', 5);

    WITH Tot
    AS (SELECT t = SUM(ct)
    FROM #t)
    SELECT t.Stat
    ,t.ct
    ,Rate = t.ct * 100 / Tot.t
    FROM #t t
    CROSS JOIN Tot;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It worked. I will try to do the same with my data and let you know! Thanks

    • This reply was modified 1 month, 3 weeks ago by  TheNewbie.
  • Honestly, I don't end up with the #t, ct etc.

    I tried to replace anything regarding my data but didnt work

    Sorry

  • TheNewbie wrote:

    Honestly, I don't end up with the #t, ct etc.

    I tried to replace anything regarding my data but didnt work

    Sorry

    Please post what you tried and I will try to help you fix it up.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • WITH Temp
    AS (SELECT tab = SUM(tab.status) -- total_nber_of_status
    FROM Table)

    SELECT tab.statut_code, -- x, y, z
    tab.status,
    Rate = tab.status * 100 / Temp.tab
    FROM Table tab
    CROSS JOIN Temp

    /*
    SELECT tab.status as status,
    COUNT(tab.status) as total_nber_of_status

    FROM table as tab
    GROUP BY tab.status
    */
  • Something like this. Calling your table 'table' is a really bad idea, because 'table' is a reserved word - that is why I have surrounded it with square brackets and aliased it.

    Also, is it really 'statut_code' rather than 'status_code'?

    WITH temp
    AS (SELECT tot = SUM(t1.status) -- total_nber_of_status
    FROM
    t1)
    SELECT t2.statut_code -- x, y, z
    ,t2.status
    ,Rate = t2.status * 100 / temp.tot
    FROM
    t2
    CROSS JOIN temp;

    Edit: that rendered horribly! Here's how it should look:

    t1

    • This reply was modified 1 month, 3 weeks ago by  Phil Parkin. Reason: Code rendering made some words invisible

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • TheNewbie wrote:

    Honestly, I don't end up with the #t, ct etc.

    I tried to replace anything regarding my data but didnt work

    Sorry

    The #t that Phil is using is what you should have done in your post... he converted your "text" into "readily consumable data" so that other people could test.  It is NOT a part of the solution... it's part of the test setup.

    Please read the article at the first link in my signature line below for why this is so very important when you post a question that looks for a coded answer.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Here's another way to post "Readily Consumable Date" followed by a different solution that uses the SUM() Windowing Function.

    Here's the test data construction...

    --===== Create the test table and populate it on-the-fly.
    -- This is NOT a part of the solution.
    -- We''re just building test data for the issue here.
    DROP TABLE IF EXISTS #TestTable;
    GO
    SELECT v.*
    INTO #TestTable
    FROM (VALUES
    ('x',2)
    ,('y',3)
    ,('z',5)
    )v([Status],StatusCount)
    ;
    GO

    Here's the solution that uses the SUM() Windowing Function...

    --===== The SUM() Windowing Function does a sum with the need for a GROUP BY, in this case.
    -- Multiplying the divisor by 100.0 not only converts the answer to a percent but it
    -- also converts the problem to a FLOAT to overcome the INTEGER MATH so that we can
    -- get a more finite answer with decimal places.
    -- The CONVERT(DECIMAL(4,1) is for formatting and could be skipped unless the boss
    -- wants it that way. :D
    SELECT [Status]
    ,StatusCount
    ,PercentOfWhole = CONVERT(DECIMAL(3,1),StatusCount*100.0/SUM(StatusCount) OVER ())
    FROM #TestTable
    ORDER BY [Status] --The only way to guarantee an order is to state it in the code.
    ;

    Here's the result...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    ...  ,PercentOfWhole = CONVERT(DECIMAL(3,1),StatusCount*100.0/SUM(StatusCount) OVER ()) ...

    I believe you want decimal(4, 1) rather than (3, 1) (at least I can't see any reason why 100% of one group might not all be the same result/value).

     

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    Jeff Moden wrote:

    ...  ,PercentOfWhole = CONVERT(DECIMAL(3,1),StatusCount*100.0/SUM(StatusCount) OVER ()) ...

    I believe you want decimal(4, 1) rather than (3, 1) (at least I can't see any reason why 100% of one group might not be the all same result/value).

    Correct and thanks for the catch, Scott.  I'm fixing it in the code now.  I appreciate it.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

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