June 9, 2014 at 2:31 am
Hello,
I am using the query below to find the number of anti-coagulant medications prescribed per hospital. It works just fine. I'd like to have a 'total' for each hospital. How do I go about this?
Thanks for any help.
SELECT *
FROM
(
SELECT Hospital, p.subjectnumber,
oralanticoagulants =
CASE OralAnticoagulantID
WHEN 1 THEN 'Warfarin'
WHEN 2 THEN 'Dabigatran'
WHEN 3 THEN 'Rivaroxaban'
WHEN 4 THEN 'Apixaban'
WHEN 5 THEN 'Sinthrome'
END
FROM tblPersonsAndOralAnticoagulants a
JOIN tblPerson p
ON p.SubjectNumber = a.SubjectNumber
)
AS s
PIVOT
(
COUNT (Subjectnumber)
FOR [OralAnticoagulants]
IN (Warfarin, Dabigatran, Rivaroxaban, Apixaban, Sinthrome
))
As P
ORDER BY Hospital
June 9, 2014 at 7:24 am
You could change your approach to a traditional CROSS TAB.
You'll end with something like this:
--Normal cross tab
SELECT Hospital,
p.subjectnumber,
COUNT ( CASE OralAnticoagulantID WHEN 1 THEN 'Warfarin' END) AS Warfarin,
COUNT ( CASE OralAnticoagulantID WHEN 2 THEN 'Dabigatran' END) AS Dabigatran,
COUNT ( CASE OralAnticoagulantID WHEN 3 THEN 'Rivaroxaban' END) AS Rivaroxaban,
COUNT ( CASE OralAnticoagulantID WHEN 4 THEN 'Apixaban' END) AS Apixaban,
COUNT ( CASE OralAnticoagulantID WHEN 5 THEN 'Sinthrome' END) AS Sinthrome,
COUNT(*)
FROM tblPersonsAndOralAnticoagulants a
JOIN tblPerson p
ON p.SubjectNumber = a.SubjectNumber
GROUP BY Hospital,
p.subjectnumber;
--Preaggregated cross tab
WITH Counts AS(
SELECT Hospital,
p.subjectnumber,
OralAnticoagulantID,
COUNT(*) Number
FROM tblPersonsAndOralAnticoagulants a
JOIN tblPerson p
ON p.SubjectNumber = a.SubjectNumber
GROUP BY Hospital,
p.subjectnumber,
OralAnticoagulantID
)
SELECT Hospital,
subjectnumber,
SUM( CASE OralAnticoagulantID WHEN 1 THEN Number END) AS Warfarin,
SUM( CASE OralAnticoagulantID WHEN 2 THEN Number END) AS Dabigatran,
SUM( CASE OralAnticoagulantID WHEN 3 THEN Number END) AS Rivaroxaban,
SUM( CASE OralAnticoagulantID WHEN 4 THEN Number END) AS Apixaban,
SUM( CASE OralAnticoagulantID WHEN 5 THEN Number END) AS Sinthrome,
SUM( Number)
FROM Counts
GROUP BY Hospital,
subjectnumber
Reference: http://www.sqlservercentral.com/articles/T-SQL/63681/
June 9, 2014 at 7:34 am
Thank you Luis,
I've tried your approach, but this is not quite what I'm looking for.
June 9, 2014 at 7:42 am
Can you be more specific? What's wrong with what I posted?
I could help you better with sample data and expected output as described on the article in my signature.
June 9, 2014 at 8:04 am
I've attached an excel spreadsheet of what I' like. I want a 'Total' column at the end which would add all the medication use for that particular hospital.
June 9, 2014 at 8:11 am
That's the expected output, where's the sample data?
My options should give you that total. How is it wrong?
June 9, 2014 at 8:12 am
I don't know this for sure, but if you're selecting p.subjectnumber, it looks to me like that's the patient number and you want a total per patient. If you eliminate the patient number from the Luis's query (both select and group by) that should give you the total by hospital.
If, on the other hand, you want a total by hospital, you might be after a ROLLUP, covered at http://technet.microsoft.com/en-us/library/bb522495%28v=sql.100%29.aspx. Make sure you sort by Hospital. I can't be sure this is what you're after, but it might get you closer to what you're trying to accomplish.
June 9, 2014 at 8:30 am
Yes, that's it. I shouldn't have included Subject Number in my original SQL - apologies.
Thanks both of you for your help.
June 9, 2014 at 8:38 am
Yes, I saw that after Ed replied and didn't want to repeat it.
Thank you for the feedback.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply