Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Add a total column Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 1:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 3:02 AM
Points: 44, Visits: 106
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



  Post Attachments 
Total.xlsx (18 views, 9.27 KB)
Post #1604301
Posted Monday, August 18, 2014 4:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
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
Post #1604355
Posted Monday, August 18, 2014 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1604409
Posted Monday, August 18, 2014 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 3:02 AM
Points: 44, Visits: 106
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?


  Post Attachments 
tblManagementOfBleeding.jpg (10 views, 113.94 KB)
tblPerson.jpg (4 views, 83.37 KB)
Post #1604420
Posted Monday, August 18, 2014 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1604445
Posted Thursday, August 21, 2014 12:06 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:04 PM
Points: 420, Visits: 996
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.
Post #1605954
Posted Thursday, August 21, 2014 1:12 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 3,908, Visits: 8,870
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1605978
Posted Friday, August 22, 2014 2:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 3:02 AM
Points: 44, Visits: 106
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

Post #1606140
Posted Friday, August 22, 2014 4:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1606182
Posted Friday, August 22, 2014 9:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 3,908, Visits: 8,870
Are you mocking my signature?
I hope that it can create awareness.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1606372
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse