Help ---- SQL Script

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       &nbs开发者_如何学JAVAp;   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

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