August 18, 2014 at 1:57 am
Hello,
I'm trying to add a 'Total' column to the statement below. The SQL works fine as is, but having a total column so I get the output attached would be great. Can someone help please?
SELECT *
FROM (
SELECT
YEAR(DateFormCompleted) AS [YearRecruited], Hospital,
left(datename(month,DateFormCompleted),3) AS [Month],
PersonCounter AS MyCounter
FROM tblPerson p
JOIN tblManagementOfBleeding m
ON m.SubjectNumber = p.SubjectNumber
)
AS s
PIVOT
(
COUNT(MyCounter) FOR [Month] IN (
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
))AS p
ORDER BY YearRecruited ASC, Hospital ASC
August 18, 2014 at 4:52 am
It would be great if you also provide table schema & sample data. Without schema & sample data, it is difficult to provide help.
Refer - http://dotnetbites.com/grand-total-pivot-sql-server and try to align as per your needs.
Thanks
August 18, 2014 at 6:27 am
Without DDL and sample data, here's my first thought:
SELECT YearRecruited, Hospital, Jan, Feb, Mar, Apr, May,
Jun, Jul, Aug, Sep, Oct, Nov, Dec, MyCounter,
(Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep
+ Oct + Nov + Dec) AS Total
FROM (
SELECT * FROM (
SELECT YEAR(DateFormCompleted) AS [YearRecruited], Hospital,
left(datename(month,DateFormCompleted),3) AS [Month],
PersonCounter AS MyCounter
FROM tblPerson p
JOIN tblManagementOfBleeding m
ON m.SubjectNumber = p.SubjectNumber)
AS s
PIVOT (
COUNT(MyCounter) FOR [Month] IN (
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
))AS p )
ORDER BY YearRecruited ASC, Hospital ASC
August 18, 2014 at 6:45 am
Hello both,
Thanks for your replies.
As to the table schema, tblPerson - tblManagementOfBleeding is a 1 ~ 1. I've attached screenshots which give more detail. Do you need anything else?
August 18, 2014 at 7:32 am
M Joomun (8/18/2014)
Hello both,Thanks for your replies.
As to the table schema, tblPerson - tblManagementOfBleeding is a 1 ~ 1. I've attached screenshots which give more detail. Do you need anything else?
Umm, yes, actually. JPGs don't give us what we need to test any code solutions. You should include CREATE TABLE statements for both tables and INSERT statements for sample code to test possible solutions against.
August 21, 2014 at 12:06 pm
Brandie Tarvin (8/18/2014)
M Joomun (8/18/2014)
Hello both,Thanks for your replies.
As to the table schema, tblPerson - tblManagementOfBleeding is a 1 ~ 1. I've attached screenshots which give more detail. Do you need anything else?
Umm, yes, actually. JPGs don't give us what we need to test any code solutions. You should include CREATE TABLE statements for both tables and INSERT statements for sample code to test possible solutions against.
Agreed. Plus I am always hesitant to open external Excel file and such. I do want to see what your final output looks like.
----------------------------------------------------
August 21, 2014 at 1:12 pm
I'd use a cross tabs approach which seems more flexible, but that's up to you.
SELECT YEAR(DateFormCompleted) AS YearRecruited,
Hospital,
COUNT(CASE WHEN month(DateFormCompleted) = 1 THEN PersonCounter END) AS 'Jan',
COUNT(CASE WHEN month(DateFormCompleted) = 2 THEN PersonCounter END) AS 'Feb',
COUNT(CASE WHEN month(DateFormCompleted) = 3 THEN PersonCounter END) AS 'Mar',
COUNT(CASE WHEN month(DateFormCompleted) = 4 THEN PersonCounter END) AS 'Apr',
COUNT(CASE WHEN month(DateFormCompleted) = 5 THEN PersonCounter END) AS 'May',
COUNT(CASE WHEN month(DateFormCompleted) = 6 THEN PersonCounter END) AS 'Jun',
COUNT(CASE WHEN month(DateFormCompleted) = 7 THEN PersonCounter END) AS 'Jul',
COUNT(CASE WHEN month(DateFormCompleted) = 8 THEN PersonCounter END) AS 'Aug',
COUNT(CASE WHEN month(DateFormCompleted) = 9 THEN PersonCounter END) AS 'Sep',
COUNT(CASE WHEN month(DateFormCompleted) = 10 THEN PersonCounter END) AS 'Oct',
COUNT(CASE WHEN month(DateFormCompleted) = 11 THEN PersonCounter END) AS 'Nov',
COUNT(CASE WHEN month(DateFormCompleted) = 12 THEN PersonCounter END) AS 'Dec',
COUNT(PersonCounter) AS 'Total'
FROM tblPerson p
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber)x
GROUP BY YEAR(DateFormCompleted),
Hospital
August 22, 2014 at 2:16 am
Luis Cazares (8/21/2014)
I'd use a cross tabs approach which seems more flexible, but that's up to you.
SELECT YEAR(DateFormCompleted) AS YearRecruited,
Hospital,
COUNT(CASE WHEN month(DateFormCompleted) = 1 THEN PersonCounter END) AS 'Jan',
COUNT(CASE WHEN month(DateFormCompleted) = 2 THEN PersonCounter END) AS 'Feb',
COUNT(CASE WHEN month(DateFormCompleted) = 3 THEN PersonCounter END) AS 'Mar',
COUNT(CASE WHEN month(DateFormCompleted) = 4 THEN PersonCounter END) AS 'Apr',
COUNT(CASE WHEN month(DateFormCompleted) = 5 THEN PersonCounter END) AS 'May',
COUNT(CASE WHEN month(DateFormCompleted) = 6 THEN PersonCounter END) AS 'Jun',
COUNT(CASE WHEN month(DateFormCompleted) = 7 THEN PersonCounter END) AS 'Jul',
COUNT(CASE WHEN month(DateFormCompleted) = 8 THEN PersonCounter END) AS 'Aug',
COUNT(CASE WHEN month(DateFormCompleted) = 9 THEN PersonCounter END) AS 'Sep',
COUNT(CASE WHEN month(DateFormCompleted) = 10 THEN PersonCounter END) AS 'Oct',
COUNT(CASE WHEN month(DateFormCompleted) = 11 THEN PersonCounter END) AS 'Nov',
COUNT(CASE WHEN month(DateFormCompleted) = 12 THEN PersonCounter END) AS 'Dec',
COUNT(PersonCounter) AS 'Total'
FROM tblPerson p
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber)x
GROUP BY YEAR(DateFormCompleted),
Hospital
Thank you Luis - that works with one minor change - see below. Thanks also to the others who tried to help. Sorry I didn't post what you requested; work has kept me busy recently.
This:
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber)x
to this:
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber
August 22, 2014 at 4:12 am
Bah... Crosstabs...
Luis Cazares (8/21/2014)
Are you seriously taking the advice and code from someone from the internet without testing it?
Why, yes. Yes I am.
Luis Cazares (8/21/2014)
Do you at least understand it?
Who needs to understanding? This is the internet age, Baby! I believe EVERYTHING I see on the internet.
Luis Cazares (8/21/2014)
Or can it easily kill your server?
Wait. Are you accusing me of servercide? I'm innocent! Innocent, I tell you!
@=)
August 22, 2014 at 9:20 am
Are you mocking my signature? :ermm:
I hope that it can create awareness. :hehe:
August 22, 2014 at 9:26 am
Luis Cazares (8/22/2014)
Are you mocking my signature? :ermm:I hope that it can create awareness. :hehe:
I am not mocking the signature. I am drawing attention to something that most people might otherwise ignore. It's a good .sig. I agree with it, but also had to have fun with it.
EDIT: I also had to make a Phineas and Ferb moment out of it. But apparently that got missed. @=)
August 22, 2014 at 10:04 am
I know you were just having fun, so was I.
I can't relate to the Phineas and Ferb moment. Should I watch more episodes? 😀
August 22, 2014 at 10:49 am
Luis Cazares (8/22/2014)
I can't relate to the Phineas and Ferb moment. Should I watch more episodes? 😀
ABSOLUTELY.
Misc. Person: Aren't you boys a little young for ...
Phineas: Why, yes. Yes we are.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply