February 22, 2013 at 3:53 am
For a clinical database which stores Patient Symptoms for Dengue Fever, I need to get the number of patients having each symptom. Symptoms are arranged as columns and 1 is Positive and 0 is Negative. I only need the Positive count.
This is the query I am using at the monent.
SELECT count([Fatigue/Weakness]), count([Loss of Appetite]), count([Rash(Macular-Papular)]), count([Headache]), count([Retro-Orbital Pain]), count([Cough]), count([Cold]), count([Sore Throat]),count([Dyspnea]), count([Nausea]), count([Vomitng]), count([Diarrhea]),count([Constipation]),
count([Petechiae]), count([Subcutaneous Hemorrhage]), count([Gum Bleeding]), count([Gum Bleeding(Days)]), count([Epistaxis]), count([Hematemesis]), count([Melaena]), count([Bleeding PV]), count([Conjunctival Hemorrhage]), count([Haematuria]), count([Bruise at Venepuncture]), count([Arthralgia]), count([Myalgia]), count([Convulsions/Seizure]), count([Jaundice]), count([Hepatomegaly(>2cm)]), count([Splenomegaly]), count([Abdominal Pain]), count([Abdominal Tenderness]), count([Hepatic(RIC) Tenderness]), count([Persistent Vomiting]), count([Lethargy]), count([Restlessness]), count([Narrow Pulse Pressure]), count([Pulse Unrecordable]), count([BP Unrecordable]), count([Skin Temperature Warm]), count([Skin Temperature Cold]), count([Capillary Refill Time(<2sec)]), count([Capillary Refill Time(>2sec)]), count([Plasma Effusion by Radiology]), count([Plasma Effusion by Clinical Examination]), count([Ascitis by Clinical Examination]), count([Ascitis by Radiology]), count([Oliguria]), count([Anuria]), count([Encephalitis]), count([Myocarditis]), count([Peripheral Oedema]), count([Respiratory Distress]), count([Pericardial Effusion]), count([Ventilated])
FROM [In_Patients_Department_DengueFever_With_Bleeding]
WHERE [] = 1;//If I am using this query I will have to coppy and paste each and every symptom to the where clause. But I cant be doing that as I have lot of reports to be prepared with same data set.
PLEASE PLEASE PLEASE HELP ME SOMEONE TO DO THIS AT ONCE.
I NEED THE COUNT OF TOTAL 1's OF EACH COLUMN
February 22, 2013 at 4:04 am
Why not use SUM()? This will count the 1s & effectively ignore the 0s.
February 22, 2013 at 11:36 am
Specifically, something like this:
SELECT sum([Fatigue/Weakness]), sum([Loss of Appetite]), sum([Rash(Macular-Papular)]), sum([Headache]), sum([Retro-Orbital Pain]), sum([Cough]), sum([Cold]), sum([Sore Throat]),sum([Dyspnea]), sum([Nausea]), sum([Vomitng]), sum([Diarrhea]),sum([Constipation]),
sum([Petechiae]), sum([Subcutaneous Hemorrhage]), sum([Gum Bleeding]), sum([Gum Bleeding(Days)]), sum([Epistaxis]), sum([Hematemesis]), sum([Melaena]), sum([Bleeding PV]), sum([Conjunctival Hemorrhage]), sum([Haematuria]), sum([Bruise at Venepuncture]), sum([Arthralgia]), sum([Myalgia]), sum([Convulsions/Seizure]), sum([Jaundice]), sum([Hepatomegaly(>2cm)]), sum([Splenomegaly]), sum([Abdominal Pain]), sum([Abdominal Tenderness]), sum([Hepatic(RIC) Tenderness]), sum([Persistent Vomiting]), sum([Lethargy]), sum([Restlessness]), sum([Narrow Pulse Pressure]), sum([Pulse Unrecordable]), sum([BP Unrecordable]), sum([Skin Temperature Warm]), sum([Skin Temperature Cold]), sum([Capillary Refill Time(<2sec)]), sum([Capillary Refill Time(>2sec)]), sum([Plasma Effusion by Radiology]), sum([Plasma Effusion by Clinical Examination]), sum([Ascitis by Clinical Examination]), sum([Ascitis by Radiology]), sum([Oliguria]), sum([Anuria]), sum([Encephalitis]), sum([Myocarditis]), sum([Peripheral Oedema]), sum([Respiratory Distress]), sum([Pericardial Effusion]), sum([Ventilated])
, count(*) --presumably you'll also want to know the total number of cases so you can derive %s for each symptom
FROM [In_Patients_Department_DengueFever_With_Bleeding]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply