counts across columns in a single table

[jlt1]

Can anyone give me some help as how to write a stored procedure to do counts across columns in a single table.
For example I have a table with columns drug1,drug2,drug3,drug4,drug5 all containing medication choices 1 to 5. I want to count for each medication across the columns so that I have a list detailing the medication and the number of users
eg
medication1 15
medication2 8
medication3 6
etc
I am a novice to SQL scripting
Can this be done?

 

[tlbroadbent]

Use sub-queries to summarize each column. Combine the sub-queries using UNOIN. Use an OUTER query for the final result.

Select Medication, UserCnt=Sum(UserCnt)
From
(Select Medication=Drug1, UserCnt=Count(*)
From Table_name
Where Drug1 Is Not Null
Group By Drug1

UNION ALL

Select Medication=Drug2, UserCnt=Count(*)
From Table_name
Where Drug2 Is Not Null
Group By Drug2

UNION ALL

Select Medication=Drug3, UserCnt=Count(*)
From Table_name
Where Drug3 Is Not Null
Group By Drug3

UNION ALL

Select Medication=Drug4, UserCnt=Count(*)
From Table_name
Where Drug4 Is Not Null
Group By Drug4

UNION ALL

Select Medication=Drug5, UserCnt=Count(*)
From Table_name
Where Drug5 Is Not Null
Group By Drug5) qry

Group By Medication
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

Sorry for my use of the following drugs or the at least the use of their names - but maybe this is a good example...

SELECT
Sum(CASE Drug1
WHEN 'PROZAC' THEN 1
ELSE 0
END
) AS CountOfDrug1,
Sum(CASE Drug2
WHEN 'XANAX' THEN 2
ELSE 0
END
) AS CountOfDrug2,
etc...
FROM DrugTable