December 7, 2015 at 2:52 am
Hi
Please tell how to calculate percentage based on column values. Below is my table
PlayerId Query1 Query2 Query3 Query4 Query5 Query6 Query7 Query8 Query9
1 A A C B B B A C D
2 B A C A C A A B B
3 A B B B A B B C A
4 C B A C A B A C C
5 B C A B C A B A A
6 B A B A B C B A B
7 A A C C B A C B C
Result should like below table
Query A% B% C% D%
Query1 43 43 14 0
Query2 57 29 14 0
--chalam
December 7, 2015 at 5:48 am
-- Set up sample data
WITH Sampledata AS (
SELECT *
FROM (VALUES
(1, 'A', 'A', 'C', 'B', 'B', 'B', 'A', 'C', 'D'),
(2, 'B', 'A', 'C', 'A', 'C', 'A', 'A', 'B', 'B'),
(3, 'A', 'B', 'B', 'B', 'A', 'B', 'B', 'C', 'A'),
(4, 'C', 'B', 'A', 'C', 'A', 'B', 'A', 'C', 'C'),
(5, 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'A', 'A'),
(6, 'B', 'A', 'B', 'A', 'B', 'C', 'B', 'A', 'B'),
(7, 'A', 'A', 'C', 'C', 'B', 'A', 'C', 'B', 'C')
) d (PlayerId, Query1, Query2, Query3, Query4, Query5, Query6, Query7, Query8, Query9)
)
SELECT *
INTO #Sampledata
FROM Sampledata;
-- Solution
SELECT
x.[Query],
[A] = CAST(ROUND(SUM(CASE WHEN x.value = 'A' THEN 100.0 ELSE 0 END)/SUM(1),0) AS INT),
= CAST(ROUND(SUM(CASE WHEN x.value = 'B' THEN 100.0 ELSE 0 END)/SUM(1),0) AS INT),
[C] = CAST(ROUND(SUM(CASE WHEN x.value = 'C' THEN 100.0 ELSE 0 END)/SUM(1),0) AS INT),
[D] = CAST(ROUND(SUM(CASE WHEN x.value = 'D' THEN 100.0 ELSE 0 END)/SUM(1),0) AS INT)
FROM #Sampledata
CROSS APPLY (
VALUES
('Query1', Query1),
('Query2', Query2),
('Query3', Query3),
('Query4', Query4),
('Query5', Query5),
('Query6', Query6),
('Query7', Query7),
('Query8', Query8),
('Query9', Query9)
) x ([Query], value)
GROUP BY x.[Query]
ORDER BY x.[Query]
See http://qa.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/[/url]
and http://qa.sqlservercentral.com/articles/T-SQL/63681/
[/url]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2015 at 6:09 am
Hi chrism,
Thanks a lot.
--chalam
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply