Add a total column

  • 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

  • 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

  • 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/[/url]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.

  • 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?

  • 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/[/url]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.

  • 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.

    ----------------------------------------------------

  • 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
  • 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

  • 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/[/url]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.

  • 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
  • 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. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]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.

  • 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? 😀

    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
  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply