CODEWITHSUNDEEP
sql-server
SRA
SRA

Reputation: 1691

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           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

Upvotes: 0

Views: 75

Answers (1)

Serge S.
Serge S.

Reputation: 4915

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

Upvotes: 2

Related Questions