Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add a total column


Add a total column

Author
Message
M Joomun
M Joomun
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 196
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


Attachments
Total.xlsx (18 views, 9.00 KB)
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
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
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7779 Visits: 8736
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



Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
M Joomun
M Joomun
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 196
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?
Attachments
tblManagementOfBleeding.jpg (10 views, 113.00 KB)
tblPerson.jpg (4 views, 83.00 KB)
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7779 Visits: 8736
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 2014
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.

----------------------------------------------------
How to post forum questions to get the best help
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
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




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
M Joomun
M Joomun
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 196
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


Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7779 Visits: 8736
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!

@=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
Are you mocking my signature? Ermm
I hope that it can create awareness. Hehe


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search