Post reply

Doubt on Query Implemantation

  • February 14, 2007 at 3:52 am

    #122979

    Hi,

            I need to generate a query.The requirement goes as follows:

                  I'm using a table tbl_processmetrics.

                             columns: distributorid, selection1, selection2,selection3

                        for the selection columns the possible vales will be 'yes' or 'no'.

                              I need to do  a calculation part like this:

                        Selection% = (100*(count('yes'))/(count('yes')+count('no'))]

                              Selection% is the summary of all the selection columns.

               Any help appreciated.

    Thank you,

    Regards,

     Deepa.

  • February 14, 2007 at 4:02 am

    #689658

    Your question is somewhat confusing. It would help if you could give some sample data and the expected results.

     

  • February 14, 2007 at 4:36 am

    #689664

    This isn't a difficult question, well my understanding of it, it would be much better if you worked through BOL, bought a SQL book or attended a training course. This is really beginners sql and I'm not sure this level of question merits a post on a forum.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 14, 2007 at 4:36 am

    #689663

    Hi Ken,

              I'm sorry for brief summary of the requirement.

          tbl_processmetrics.

                           distributorid          selection1            selection2          selection3         selection%

                                   1001               yes                      yes                      no

                                    1002               yes                      no                       yes   

     I need to get the selection percentage for a distributorid.

    The calculation part will be like this (100*(count('yes'))/(count('yes')+count('no')).

    Counting the no of 'yes' and 'no' for each distributorid.

         Selection% will be the percentage of the three selection columns.

    I hope I explained better.

    Thank you,

    Regards,

    Deepa.

         

  • February 14, 2007 at 4:43 am

    #689666

    Hi,

            Thanks a lot for your suggestion.

    Regards,

    Deepa.

  • February 14, 2007 at 5:03 am

    #689669

    As you only want the totals for each row the query is, as Colin indicated, quite straigtht forward.

    (Although it would be better if you normalized the table!)

    Try:

    SELECT distribuorid, selection1, selection2, selection3

            ,Yeses * 100 / (Yeses + Nos) AS selectionPercent

    FROM (

            SELECT distribuorid, selection1, selection2, selection3

                    ,CASE WHEN selection1 = 'Yes' THEN 1 ELSE 0 END

                    + CASE WHEN selection2 = 'Yes' THEN 1 ELSE 0 END

                    + CASE WHEN selection3 = 'Yes' THEN 1 ELSE 0 END AS Yeses

                    ,CASE WHEN selection1 = 'No' THEN 1 ELSE 0 END

                    + CASE WHEN selection2 = 'No' THEN 1 ELSE 0 END

                    + CASE WHEN selection3 = 'No' THEN 1 ELSE 0 END AS Nos

            FROM tbl_processmetrics

    ) D

  • February 14, 2007 at 7:40 pm

    #689863

    Hi,

    I implemented using functions.Anyhow Thanks a lot for spending your valuable time.

    Thank you,

    Regards,

    Deepa.

Viewing 7 posts - 1 through 7 (of 7 total)

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