Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Help with computing rates

    Hello,

    I have a table as such:

    UniqueID Email PlayerAction PlayRate DNPRt NoShowRt
    1 fake@acme.com Played
    2 fake@acme.com Played
    3 fake@acme.com Did not play
    4 fake@acme.com Played
    5 fake@acme.com No show

    I need to come up with the play rate, did not play rate, and no showrate for this table. In this case, the play rate would be 60% (or 3/5). I'm not sure how to come up with the SQL for this, though. Can someone please shed some light?

    TIA

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    naceBal, I don't have access to SQL Server so I can't test this out. But, if you have SQL Server 2005 or 2008, this should work (with the possible addition of CAST the results if integer datatypes don't produce the decimal results):
    Code:
    WITH CTE
      AS (SELECT EMAIL
               , SUM(1) AS DECIMAL(3,2)) AS TOTALCNT
               , SUM(CASE PLAYERACTION WHEN 'Played'       THEN 1 ELSE 0 END) AS PLAYEDCNT
               , SUM(CASE PLAYERACTION WHEN 'Did not play' THEN 1 ELSE 0 END) AS DNPCNT
               , SUM(CASE PLAYERACTION WHEN 'No show'      THEN 1 ELSE 0 END) AS NOSHOWCNT
          FROM DATATAB
          GROUP BY EMAIL
         )
    SELECT EMAIL
         , PLAYEDCNT / TOTALCNT AS PLAYRATE
         , DNPCNT    / TOTALCNT AS DNPRT
         , NOSHOWCNT / TOTALCNT AS NOSHOWRT
    FROM CTE
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •