none
Calculating weightage based on value in sql RRS feed

  • Question

  • Hello Techie,

    I have little confusion on deign phase of reporting.

    My Table looks like 

    DDL:

    DECLARE @responsetable TABLE (
    CUSTOMER varchar(50),
    GOTRESPONSE int
    );
    
    INSERT @responsetable
    
    SELECT 'CUST_2634',1 UNION ALL
    SELECT 'CUST_85',1  UNION ALL
    SELECT 'CUST_976',2  UNION ALL
    SELECT 'CUST_3005',2  UNION ALL
    SELECT 'CUST_1594',10  UNION ALL
    SELECT 'CUST_519',10
    
    SELECT * FROM @responsetable

    i am facing difficulties, what aggregate function should i used to show response_rate which is based on COUNT(GotResponse), 
    shall i use Mean , or median or percentage or something else. 
    Customer who got lowest Gotresponse count SHOULD have lowest weight, 
    and the one who got highest Gotresponse count get highest weight. IN WEIGHT COLUMN

    Please help me, i know this is not specific problem question, 
    but i am requesting for design help. kindly share your expertise knowledge. what could be best option.

    Expected Output table looks like

     

    CUSTOMER,GOTRESPONSE,WEITAGE




    Friday, July 31, 2020 3:26 PM

All replies

  • I would assume you may want to use percentage, e.g. sum of gotresponse per customer vs sum or gotresponse for all customers.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, July 31, 2020 3:57 PM
    Moderator
  • It's hard to know exactly what you want.  If I understand what you want, one possibility might be to show the weight value as the GOTRESPONSE value as a percentage of the sum of all the GOTRESPONSE values.  That would look like

    Select CUSTOMER, GOTRESPONSE, Cast(100.*GOTRESPONSE/Sum(GOTRESPONSE) Over() As decimal(5,2)) As WEITAGE 
    From @responsetable
    Order By WEITAGE Desc, CUSTOMER;

    Tom

    Sunday, August 2, 2020 5:19 AM
  • Hi William,

    The expected output table did not show in your reuqirement.

    Please also have a try with below:

    DECLARE @responsetable TABLE (
    CUSTOMER varchar(50),
    GOTRESPONSE int
    );
    
    INSERT @responsetable
    
    SELECT 'CUST_2634',1 UNION ALL
    SELECT 'CUST_85',1  UNION ALL
    SELECT 'CUST_976',2  UNION ALL
    SELECT 'CUST_3005',2  UNION ALL
    SELECT 'CUST_1594',10  UNION ALL
    SELECT 'CUST_519',10
    
    Select CUSTOMER,GOTRESPONSE, 
    convert(decimal(5,2),(GOTRESPONSE* 100. / (Select sum(GOTRESPONSE) From @responsetable))) as WEITAGE
    From @responsetable
    group by CUSTOMER,GOTRESPONSE
    order by CUSTOMER
    
    /*
    CUSTOMER	GOTRESPONSE	WEITAGE
    CUST_1594	10	38.46
    CUST_2634	1	3.85
    CUST_3005	2	7.69
    CUST_519	10	38.46
    CUST_85	1	3.85
    CUST_976	2	7.69
    */

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, August 3, 2020 2:36 AM
  • Hi William,

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

     

    Thank you for understanding!

     

     

    Best regards,

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 7:33 AM
  • My Table looks like 

    The first thing we need to do is repair your attempt at DDL. Did you know that table must have a key, by definition? There's no such thing as just "customer" ; very clearly a customer_id is an entity in a table somewhere. You also don't seem to know the syntax for an insertion statement. Please read a good book on SQL that includes something about the ANSI/ISO standards.

    CREATE TABLE Survey_Responses
    (customer_id VARCHAR(10) NOT NULL PRIMARY KEY, --required!!
    response_cnt DEFAULT 0 INTEGER NOT NULL 
     CHECK (response_cnt>= 0));

    I made a guess that your responses are a count. But they might be on some kind of ordinal scale. I also had to make a guess about a check constraint on that column

    INSERT INTO Survey_Responses
    VALUES
    ('CUST_2634', 1), 
    ('CUST_85', 1), 
    ('CUST_976', 2), 
    ('CUST_3005', 2), 
    ('CUST_1594', 10), 
    ('CUST_519', 10);

    Please notice the current syntax for insertion statements.

    >> I am facing difficulties, what aggregate function should I used to show response_rate which is based on response_cnt. Shall I use Mean or median or percentage or something else. << 

    AVG() is the fastest way, but a median is a better measure of central tendency. I do not see a "response_wgt" computation. 

    >> customer_id who got lowest response_cnt SHOULD have lowest weight, and the one who got highest response_cnt get highest weight. IN WEIGHT COLUMN <<

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, August 4, 2020 10:57 PM