March 22, 2013 at 6:59 pm
Hi,
I need to make a report from a table contains following data (I made a simple structure as the original table includes many columns for other information) :
DECLARE @Test_TBL TABLE (Sample_ID VARCHAR(3),AntiBiotic VARCHAR(20), Result INT)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('1', 'AMP', 1)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('1', 'TET', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('2', 'SPT', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('3', 'AMP', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('3', 'SPT', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('4', 'AMP', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('4', 'TET', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('4', 'STR', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('5', 'AMP', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('5', 'STR', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('6', 'TET', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('6', 'SPT', 1)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('6', 'STR', 2)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('7', '', 0)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('8', '', 0)
INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('9', '', 0)
In this table "Result" column shows the result of antibiotic resistance test (0=not tested, 1=non-resistant, 2=resistant).
I need to have report like :
Antibiotic No. of tests No. of resistant sample % of resistant
------------ ------------- ---------------------------- ----------------
AMP 4 3 75.0
.......
Please notice that not all antibiotics are tested for all samples.
I appreciate for helps.
March 23, 2013 at 7:29 am
SELECT Antibiotic
, SUM(CASE WHEN Result IN (1, 2) THEN 1 ELSE 0 END) AS Tests
, SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS ResistSampls
, ROUND(
CAST(SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS FLOAT)
/
CAST(SUM(CASE WHEN Result IN (1, 2) THEN 1 ELSE 0 END) AS FLOAT)
* 100,
3) AS PctResistant
FROM @Test_TBL
GROUP BY Antibiotic
Note that we're casting the numerator and denominator to a float *before we perform the division*. We do this because dividing an INT by an INT results in an INT, not a decimal number. So we cast prior to the division to get a decimal result. We're also multiplying by 100 and rounding to 3 decimal points.
March 23, 2013 at 7:37 am
ryan.mcatee (3/23/2013)
SELECT Antibiotic, SUM(CASE WHEN Result IN (1, 2) THEN 1 ELSE 0 END) AS Tests
, SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS ResistSampls
, ROUND(
CAST(SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS FLOAT)
/
CAST(SUM(CASE WHEN Result IN (1, 2) THEN 1 ELSE 0 END) AS FLOAT)
* 100,
3) AS PctResistant
FROM @Test_TBL
GROUP BY Antibiotic
Note that we're casting the numerator and denominator to a float *before we perform the division*. We do this because dividing an INT by an INT results in an INT, not a decimal number. So we cast prior to the division to get a decimal result. We're also multiplying by 100 and rounding to 3 decimal points.
Dear Ryan,
Thanks for your solution. It worked perfectly. I just added a "WHERE" clause to avoid the "Divide by zero error encountered." error message. so it became like :
SELECT Antibiotic
, SUM(CASE WHEN Result IN (1, 2) THEN 1 ELSE 0 END) AS Tests
, SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS ResistSampls
, ROUND(
CAST(SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS FLOAT)
/
CAST(SUM(CASE WHEN Result IN (1, 2) THEN 1 ELSE 0 END) AS FLOAT)
* 100,
3) AS PctResistant
FROM @Test_TBL WHERE Antibiotic <> ''
GROUP BY Antibiotic
March 23, 2013 at 8:23 am
Or add a WHERE clause on the Result column?
SELECTAntibiotic,
SUM(1) AS [No. of tests],
SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS [No. of resistant sample],
100E * SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) / SUM(1) AS [% of resistant]
FROM@Test_TBL
WHEREResult IN (1, 2)
GROUP BYAntibiotic;
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply