# Calculating weightage based on value in sql

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

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

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 !

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