Help ---- SQL Script

Question

Store No Store Name Region Division Q10(response) Q21(response)

2345     ABC              North Test              1                       5
2345                            North Test              6                       3
2345       ABC              North Test              4                       6

1st calculation

1 ) Engaged(%) = Response Greater than 4.5
3 (total response greater than 4.5) / 6 (total count) * 100 = 50%

Store No Store Name Region Division Q10 Q21
2345             ABC      North Test           1       5
2345             ABC      North Test           6       3
2345            ABC       North Test           4       6

2) not engaged (%) = Response less than 2
1 (total response less than 2) / 6 (total count) * 100 = 16.66%

I should be able to get the table like this

Store No Store Name Region Division Engaged(%) Disengaged(%)
2345 ABC North Test 50 16.66

Serge S. · Accepted Answer · 2010-05-10 07:04:17Z

2

Your statistics rounded 2 digits after point (T-SQL), grouped by stores:

SELECT
    store_no,
    store_name,
    region,
    division,
    ROUND( CAST( (SUM(CASE WHEN q10 > 4.5 THEN 1 ELSE 0 END) + SUM(CASE WHEN q21 > 4.5 THEN 1 ELSE 0 END)) AS FLOAT) / (COUNT(q10)+COUNT(q21)) * 100, 2)   AS engaged,
    ROUND( CAST( (SUM(CASE WHEN q10 < 2 THEN 1 ELSE 0 END) + SUM(CASE WHEN q21 < 2 THEN 1 ELSE 0 END)) AS FLOAT) / (COUNT(q10)+COUNT(q21)) * 100, 2)  AS not_engaged
FROM
    yourtable
GROUP BY
    store_no,
    store_name,
    region,
    division

edited May 10, 2010 at 7:04

answered May 10, 2010 at 6:48

Serge S.

4,9353 gold badges44 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Unreason Over a year ago

+1 That should do it. Note on COUNT(*)*2 - it ignores NULLs; if these need to be handled better write it as COUNT(q10)+COUNT(q21)

Collectives™ on Stack Overflow

Help ---- SQL Script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related