case when with count ms sql

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Thank you Phil

  • Jeff Moden wrote:

    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.

     

    Ok Jeff I will read the article. Thanks

  • Phil Parkin wrote:

    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

     

    Im going to be crazy!!!

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f95a88bddb4ab046ea4f0b5afe65e840

    I've done the same thing with my code and here is what I got:

    firstly this is how I get all status and their total sum

    SELECT X.status      as  status_code -- x, y, z
    count(X.status_ID) as status_count -- status_ID contains letters i.e. 'A1A00001'
    FROM myTable X
    GROUP BY X.status

    with this code I have the result Im looking for (first step)

    |status_code|status_count|
    | x |2 |
    | y |3 |
    | z |5 |

    but now when Im using your code (to have the percentage) firstly with 'SUM' I got

    WITH Temp
    AS (SELECT t = SUM(status_count)
    FROM my_table)
    SELECT t.status
    ,t.status_count
    ,Rate = t.status_count * 100 / Temp.t
    FROM my_table t
    CROSS JOIN Temp;

    'Operand data type varchar is invalid for sum operator.'

    and with COUNT I got

    'Conversion failed when converting the varchar value 'A1A00001' to data type int.'

     

    Any help

  • Jeff Moden wrote:

    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...

     

    Hi Jeff,

    I tested your code

    SELECT X.status      as  status_code -- x, y, z
    rate = CONVERT(DECIMAL(4,1), status_count *100.0 / SUM(status_count ) OVER ())-- status_ID contains ----letters i.e. 'A1A00001'
    --count(X.status_ID) as status_count
    FROM myTable X
    GROUP BY X.status, status_count
    --ORDER BY X.status

    I have this error

    'Operand data type varchar is invalid for sum operator.'

  • What is the datatype of the column 'status_count'?

    We have assumed that it is an integer, but I suspect that it is not.

    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.

  • Phil Parkin wrote:

    What is the datatype of the column 'status_count'?

    We have assumed that it is an integer, but I suspect that it is not.

     

    varchar cause there are letter in it  'A1A00001'

    status_count as you already know is given/calculated throught status_ID (which are varchar)

  • TheNewbie wrote:

    Phil Parkin wrote:

    What is the datatype of the column 'status_count'?

    We have assumed that it is an integer, but I suspect that it is not.

    varchar cause there are letter in it  'A1A00001'

    status_count as you already know is given/calculated throught status_ID (which are varchar)

    How can a count contain non-numeric characters?

    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.

  • Phil Parkin wrote:

    TheNewbie wrote:

    Phil Parkin wrote:

    What is the datatype of the column 'status_count'?

    We have assumed that it is an integer, but I suspect that it is not.

    varchar cause there are letter in it  'A1A00001'

    status_count as you already know is given/calculated throught status_ID (which are varchar)

    How can a count contain non-numeric characters?

     

    I think I have to be concentrated 🙁

    I dont need to have  status_count using the status_ID (which are varchar)

    If you look at the code right now you will notice how simple I should have done it but till for the percentage it doesnt work but I think Im not far away from the solution 🙂

    |status_code|
    | x |
    | x |
    | y |
    | y |
    | y |
    | z |
    | z |
    | z |
    | z |
    | z |

    SELECT X.status as status_code -- x, y, z
    count(X.status) as status_count
    FROM myTable X
    GROUP BY X.status

    |status_code|status_count|
    | x |2 |
    | y |3 |
    | z |5 |

    SELECT X.status as status_code -- x, y, z
    rate = CONVERT(DECIMAL(4,1), status_code *100.0 / SUM(status_code ) OVER ())
    --count(X.status_ID) as status_count
    FROM myTable X
    GROUP BY X.status
    --ORDER BY X.status

    |status_code|status_count| rate |
    | x |2 |20.0 |
    | y |3 |30.0 |
    | z |5 |50.0 |
  • when I try your hardcoded logic   I get  0 for X and 100 for y

     select 25/40 *100 
    select 100 - ((25/40)*100)

    Based on your logic same result 0 for X and 100 for y

    Create TABLE #temp
    (ID int
    , ID_Status varchar(5)

    )
    Insert into #temp values (25,'x')
    Insert into #temp values (15,'y')


    select * from #temp

    select (ID), ID_Status
    ,Rate = case
    when ID_Status= 'x' then cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
    when ID_Status= 'y' then 100 - cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
    end
    from #temp

    • This reply was modified 1 month, 3 weeks ago by  Madhu g.
    • This reply was modified 1 month, 3 weeks ago by  Madhu g.
  • Madhu g wrote:

    when I try your hardcoded logic   I get  0 for X and 100 for y

     select 25/40 *100 
    select 100 - ((25/40)*100)

    Based on your logic same result 0 for X and 100 for y

    Create TABLE #temp
    (ID int
    , ID_Status varchar(5)

    )
    Insert into #temp values (25,'x')
    Insert into #temp values (15,'y')


    select * from #temp

    select (ID), ID_Status
    ,Rate = case
    when ID_Status= 'x' then cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
    when ID_Status= 'y' then 100 - cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
    end
    from #temp

     

    Hi Madhu,

    I think the logic looks more like this of what I want to achieve . Its like to calculate an average

    2,3,5 avg (2+3+5)/3= 3.33

    BUT the percentage for each status will be

    (2/3)*100 for x

    (3/3)*100 for y

    (5/3)*100 for z

    SELECT X.status      as  status_code, -- x, y, z
    COUNT(X.status) as status_count ,
    (CASE
    WHEN X.status = 'x' THEN (COUNT(x)/SUM((X.status))*100
    ELSE
    WHEN X.status = 'y' THEN (COUNT(y)/SUM((X.status))*100
    ELSE (COUNT(z)/SUM((X.status))*100 END ) AS rate

    FROM myTable X
    GROUP BY X.status

    • This reply was modified 1 month, 2 weeks ago by  TheNewbie.

Viewing 12 posts - 16 through 26 (of 26 total)

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