January 4, 2018 at 11:29 am
I have a table of items and their class rank. I need to count the class and divide by the total number of items. Here is a sample of 10 items across 4 classes. What I hope to get back is the percentage level of the classes based on the overall items, so I'd hope to get A=.3, B=.2, C=.4 and D=.1 based on the table below. I'm stuck on the formula though.
CREATE TABLE jc_item_class (item_id varchar(40), class varchar(10))
INSERT INTO jc_item_class (item_id, class)VALUES ('ABC', 'A'),('DEF', 'A'),('HIJ', 'A'),('KLM', 'B'),('NOP', 'B'),
('QRS', 'C'),('TUV', 'C'),('WXY', 'C'),('Z12', 'C'),('Z34', 'D')
SELECT class , COUNT(CASE WHEN class = 'A' THEN (item_id) END) / COUNT(item_id)
FROM jc_item_class GROUP BY class
January 4, 2018 at 11:41 am
jcobb 20350 - Thursday, January 4, 2018 11:29 AMI have a table of items and their class rank. I need to count the class and divide by the total number of items. Here is a sample of 10 items across 4 classes. What I hope to get back is the percentage level of the classes based on the overall items, so I'd hope to get A=.3, B=.2, C=.4 and D=.1 based on the table below. I'm stuck on the formula though.CREATE TABLE jc_item_class (item_id varchar(40), class varchar(10))
INSERT INTO jc_item_class (item_id, class)VALUES ('ABC', 'A'),('DEF', 'A'),('HIJ', 'A'),('KLM', 'B'),('NOP', 'B'),
('QRS', 'C'),('TUV', 'C'),('WXY', 'C'),('Z12', 'C'),('Z34', 'D')SELECT class , COUNT(CASE WHEN class = 'A' THEN (item_id) END) / COUNT(item_id)
FROM jc_item_class GROUP BY class
I think you want something like this:
SELECT
class
, COUNT(class)/ ((SELECT COUNT(Item_id) FROM jc_item_class) * 1.0)
FROM jc_item_class
GROUP BY class;
January 4, 2018 at 12:05 pm
So simple. Thanks so much, works like a charm.
January 4, 2018 at 1:48 pm
Here's a different way. It might be better or worse, so you should test.
WITH CTE AS(
SELECT class, COUNT(item_id) Item_count
FROM jc_item_class
GROUP BY class
)
SELECT class, CAST(Item_count AS float) / SUM(Item_count) OVER()
FROM CTE;
January 4, 2018 at 2:13 pm
Luis Cazares - Thursday, January 4, 2018 1:48 PMHere's a different way. It might be better or worse, so you should test.
WITH CTE AS(
SELECT class, COUNT(item_id) Item_count
FROM jc_item_class
GROUP BY class
)
SELECT class, CAST(Item_count AS float) / SUM(Item_count) OVER()
FROM CTE;
Here is the statistics results using the tiny sample data set, your first:
Table 'Worktable'. Scan count 3, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'jc_item_class'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'jc_item_class'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'jc_item_class'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply