Post reply

Showing Sum in the last row

  • June 16, 2012 at 1:27 am

    #276414

    Hi

    i have a table with some rows. what i want is i want to show the sum of a particular field in the last row that will get generate automatically. example:

    i have records like this

    Id name salary

    1 Bob 2000

    2 Jeff 3000

    3 John 5000

    Output should be:

    Id name salary

    1 Bob 2000

    2 Jeff 3000

    3 John 5000

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

    10000 -- this row will generate automatically through query

  • June 16, 2012 at 8:02 am

    #1501958

    One way would be to just create a view and union the sum total to the bottom. Unfortunately I don't think you can utilize ROLLUP, as you aren't really grouping by anything.

    create table NameTable

    (

    id int identity(1, 1) not null,

    Name nvarchar(32) not null,

    Value int not null

    );

    go

    insert into NameTable(Name, Value)

    values

    ('Bob', 2000),

    ('Sue', 3000),

    ('George', 5000);

    go

    create view DataWithSum

    as

    select *

    from NameTable

    union all

    select

    null,

    'Total',

    sum(Value)

    from NameTable;

    go

    select *

    from DataWithSum;

    The result set will look like this:

    idNameValue

    1Bob2000

    2Sue3000

    3George5000

    NULLTotal10000


    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • June 16, 2012 at 11:37 am

    #1501971

    Although this can be done, it seems like what you want is actually a report that does the sum for you. You should not be doing this with an SQL query. You do it in the presentation layer.

    Jared
    CE - Microsoft

  • June 16, 2012 at 11:54 am

    #1501972

    SQLKnowItAll (6/16/2012)

    You should not be doing this with an SQL query. You do it in the presentation layer.

    Why? Not picking on you specifically. It's just that a whole lot of people say that and they never offer an explanation and they never even ask if there's an application involved.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 16, 2012 at 12:08 pm

    #1501973

    Thomas Stringer (6/16/2012)

    Unfortunately I don't think you can utilize ROLLUP, as you aren't really grouping by anything.

    Do what I do... Cheat it. Groups of "1" are still groups. 🙂

    SELECT ID,

    Name = CASE WHEN GROUPING(Name) = 0 THEN Name ELSE 'Total' END,

    Value = SUM(Value)

    FROM dbo.NameTable

    GROUP BY ID, Name WITH ROLLUP

    HAVING GROUPING(Name) = 0

    OR GROUPING(ID) = 1

    ;

    ID Name Value

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

    1 Bob 2000

    2 Sue 3000

    3 George 5000

    NULL Total 10000

    (4 row(s) affected)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 18, 2012 at 5:58 am

    #1502189

    Jeff Moden (6/16/2012)

    SQLKnowItAll (6/16/2012)

    You should not be doing this with an SQL query. You do it in the presentation layer.

    Why? Not picking on you specifically. It's just that a whole lot of people say that and they never offer an explanation and they never even ask if there's an application involved.

    I suppose I never really asked this question to even myself, so I had to think on it a bit...

    The first reason is because then you are not working with an independent set of data. You now have a set of data and some "attribute" or description of it. However, they are not separate and thus cannot be treated as separate in the output. I believe that SQL should return data as a set that can be worked with; i.e. return the set of data and let a report take that set and display it and do with it what it needs to. Tools for reporting and manipulating sets of data for statistics exist for that purpose. SQL exists to store and manipulate data as sets. So, when working with a set of data I believe it is much easier and more flexible to use a presentation layer to arrange and display your data. The "total" when combined with the set of data that it is computed from should not be considered as part of that set.

    Probably a better way to explain this, but it is still early for me and I had a busy weekend 🙂

    Jared
    CE - Microsoft

  • June 18, 2012 at 7:27 am

    #1502224

    SQLKnowItAll (6/18/2012)

    So, when working with a set of data I believe it is much easier and more flexible to use a presentation layer to arrange and display your data.

    Fair enough. Let's put that notion to the test. Let's say I send data to the front end or reporting tool and it does this "grand total". Now, the requirements change. Lets say that we suddenly have more than one value per person and they want to see all of the values for each person, a sub-total for each person, and, of course, the existing grand total.

    Now the question is, if it's front end code, what changes must you make and will you have to redeploy code. Same goes for the reporting app.

    Now, ask yourself what changes you need to make to my code to do ALL of that and how much code I'll need to retest and redeploy.

    Let's change the requirements again. Let's just say that the only thing that changed was the fact that there will be multiple entries per person. How much code will need to change in the front end vs the simple code I wrote? Also, how much more data would have to be sent over the pipe to the app/reporting tool compared to the preaggregated data I send?

    I agree that formatting dates, currency, etc is a usual "Bozo-no-no" but you have to consider the ramifications of what happens to the pipe and how easy it is to make changes before you say "That should always be done in the app."

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 18, 2012 at 7:38 am

    #1502232

    Jeff Moden (6/18/2012)

    SQLKnowItAll (6/18/2012)

    So, when working with a set of data I believe it is much easier and more flexible to use a presentation layer to arrange and display your data.

    Fair enough. Let's put that notion to the test. Let's say I send data to the front end or reporting tool and it does this "grand total". Now, the requirements change. Lets say that we suddenly have more than one value per person and they want to see all of the values for each person, a sub-total for each person, and, of course, the existing grand total.

    Now the question is, if it's front end code, what changes must you make and will you have to redeploy code. Same goes for the reporting app.

    Now, ask yourself what changes you need to make to my code to do ALL of that and how much code I'll need to retest and redeploy.

    Let's change the requirements again. Let's just say that the only thing that changed was the fact that there will be multiple entries per person. How much code will need to change in the front end vs the simple code I wrote? Also, how much more data would have to be sent over the pipe to the app/reporting tool compared to the preaggregated data I send?

    I agree that formatting dates, currency, etc is a usual "Bozo-no-no" but you have to consider the ramifications of what happens to the pipe and how easy it is to make changes before you say "That should always be done in the app."

    Touche! However, in this specific case and the ones mentioned, the data going down the pipe does not decrease because all rows, plus sub-totals are being displayed. This will probably also require a front end change unless this is simply spitting out non-formatted data. In which case, I would find it terribly confusing to not have proper separation with formatting of the data. Of course, if all of the data will be pre-aggregated, then I would do that in SQL as a set. Furthermore, I would "personally" find it much easier to re-write the front end to format it than trying to do it in SQL, especially if we are placing subtotals at the bottom of each grouping and then a final total. Of course, this would depend on your familiarity with the tool being used and how complicated that tool is.

    However, I see your point that it (like many other things) is not a definite rule that you should not use SQL for these things. It will depend on many factors including the expectations of the end-user.

    Jared
    CE - Microsoft

  • June 18, 2012 at 4:15 pm

    #1502495

    SQLKnowItAll (6/18/2012)

    I would find it terribly confusing to not have proper separation with formatting of the data

    But that's my whole point. Who says that calculating totals is 1) formatting and 2) that it must be done in the front end to have "proper separation"? Also, even though the current requirements have just one entry per person, isn't it better to make code more bullet-proof by making it less-likely to require a redeployment for a simple change in requirements (which I've seen happen more often than not for such simple things).

    Heh... sure, someone may have made the mistake of formatting totals differently than the rest and, if they have, then there's no way to help from the backend. They're definitely stuck with making front-end changes. If they're using something like a "matrix" report in SSRS, then letting the report come up with the totals is definitely a good idea.

    The thing I don't want to have people do is to stop thinking about where to do such a thing even though the generally accepted rule is to do it in the front end. Folks have to know the reasons why that might be a better idea in a given instance but they also need to know why it might not.

    Anyway, thanks for your fine replies. Always good talking with you.

    {Edit} Fix Typos

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 18, 2012 at 4:33 pm

    #1502499

    Hope you don't mind if I chime in on this discussion Jeff. 🙂

    Jeff Moden (6/18/2012)

    SQLKnowItAll (6/18/2012)

    So, when working with a set of data I believe it is much easier and more flexible to use a presentation layer to arrange and display your data.

    Fair enough. Let's put that notion to the test. Let's say I send data to the front end or reporting tool and it does this "grand total". Now, the requirements change.

    Don't they always?

    Lets say that we suddenly have more than one value per person and they want to see all of the values for each person, a sub-total for each person, and, of course, the existing grand total.

    Okay, my first question to this is are you simply dumping unformatted data to the end user, or will the front end be changing as well to accomodate this, too?

    Now the question is, if it's front end code, what changes must you make and will you have to redeploy code. Same goes for the reporting app.

    A few clicks to include a sub-group in an SSRS table and a redeployment of the .rdl without having to retest underlying data-selection mechanics to confirm no data-retrieval operations were modified inadvertantly. Crystal's about as simple. ASP and the like are a whole different ballgame that I can't discuss directly nor intelligently.

    Now, ask yourself what changes you need to make to my code to do ALL of that and how much code I'll need to retest and redeploy.

    To dump raw data? Not much. But at that point it's not a report to me, or front end display, it's a data dump. You could in theory just hand them a CSV if that's the only change made.

    Let's change the requirements again. Let's just say that the only thing that changed was the fact that there will be multiple entries per person. How much code will need to change in the front end vs the simple code I wrote?

    Neither should change if done correctly. It's simply group sum mechanics in the front end, they're designed to do what SQL fights to do, in particular rollups. EDIT: I meant running totals here, sorry. [/EDIT]

    Also, how much more data would have to be sent over the pipe to the app/reporting tool compared to the preaggregated data I send?

    Since you're reporting the lowest level as well as the rollups? You'll be sending more by sending the subgroup totals, not less. In general treat report data like BI fact data for SSAS. Only send it the lowest level of detail it needs and let it run with it from there.

    I agree that formatting dates, currency, etc is a usual "Bozo-no-no" but you have to consider the ramifications of what happens to the pipe and how easy it is to make changes before you say "That should always be done in the app."

    Then there's things like dynamic pivots that are heavily done in T-SQL and really should be done at the report level. One of the reasons is the dynamic metadata, where-as the report (good ones anyway) should have the ability to twist your data without needing to be redefined every time your underlying metadata has changed.

    There's also one final reason to push these tasks out to the application... because it scales cheaper. One reason I DON'T like SSRS is you have to license each server it runs on so other systems can be more reasonable for the TOC. However, by pushing that 'weight' out to a front end system your SQL Server has one less task that it doesn't need to do running on it, saving you licensing fees and space you may need for other things that DO need the processing power, like properly getting the Top 1 for latest record recovery... instead of sorting and totaling some information that the front end is equally as capable of doing for less cost once you hit system limits.

    I'm not saying under no circumstances should you be performing formatting and rollups at the SQL layer, particularly for file data dumps and the like. Under most circumstances, however, I would always investigate the final layer first for the availability of doing these tasks easily and efficiently before dropping it to the SQL Server.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 18, 2012 at 8:24 pm

    #1502555

    Evil Kraig F (6/18/2012)

    Hope you don't mind if I chime in on this discussion Jeff. 🙂

    How can I say "No" to someone who wants to discuss something that I started? Thanks for jumping in.

    It's funny. I both agree and disagree with everything you brought up. Why and how can I do so? Because, "It Depends" and that's what I was trying to get across to Jared. You can't just assume that things should be done in one place or another. Sure, there are some rules of thumb but you really should know both methods and the advantages and disadvantages of both methods before you can make a proper decision for any given task. As you said, sending a "full data dump" to a reporting tool probably isn't a good idea. You'd send pre-aggregated data by person, place, thing, or date unless the raw data was required. If that happened, you'd send only the raw data and let the app do the sub-totals and totals... well, maybe :-). "It Depends". Yes, you could do the same with preaggregated data quite effectively. But, do you know your business? Can your "code" support the required spreadsheet for some, the "report" for others, PowerPivot or other DW based system for others, etc, etc? Are any of those required or are all of them required? If you send the data without the totals, can your target audiance actually come up with the totals on their own? Even if they can, would you save them some time by calculating it for them? Can you anticipate a future addition or change to the requirements and the possible reporting method and bullet proof the code to withstand it all in the least expensive manner possible? Patently, if the answer to those things is "No", then have at it. Build totals and other calculated functionality where ever your heart desires.

    Heh... I know. Yada-yada-yada. For every point we each make on this subject, the other can come up with just as valid a counter point. You know why? Because "IT DEPENDS".

    For me, it means that building totals in the front-end isn't always the best thing thing to do as some folks will advertise. And how are you going to do that dynamic Pivot that you're talking about if you have no reporting tools or app and still need to do the pivot to produce a file? For me, the answer is and always will be "It Depends" on who needs it for what and what you have to work with. That's what I want folks to take away from this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 18, 2012 at 8:38 pm

    #1502557

    Jeff Moden (6/18/2012)

    But, do you know your business? Can your "code" support the required spreadsheet for some, the "report" for others, PowerPivot or other DW based system for others, etc, etc? Are any of those required or are all of them required? If you send the data without the totals, can your target audiance actually come up with the totals on their own? Even if they can, would you save them some time by calculating it for them? Can you anticipate a future addition or change to the requirements and the possible reporting method and bullet proof the code to withstand it all in the least expensive manner possible?

    The answer to these, in general, is you're not usually dealing with power users thus the patented answer of 'Do it in the front end!' because you're going to end up doing it for them ANYWAY... usually. That said...

    Heh... I know. Yada-yada-yada. For every point we each make on this subject, the other can come up with just as valid a counter point. You know why? Because "IT DEPENDS".

    Laugh, quite true. This is a merry-go-round argument without a specific case study, to be sure. 😉

    For me, the answer is and always will be "It Depends" on who needs it for what and what you have to work with. That's what I want folks to take away from this.

    Oh, I agree, but with caveats. That's why one of my questions when I see folks doing heavy lifting like totals in the same result set or heavy duty pivots is "What is the final destination for this data?" Sometimes the answer is learning the tool, sometimes it's 'Oh just do it in SQL', and sometimes it's me scratching my head because I have no idea about ReportWriter 2040 Version 8.222 or whatnot. 🙂

    I would say, though, as a rule of thumb, it's not 'do it in the front end', it's 'see if you CAN do it in the front end, then work backwards'. T-SQL shouldn't be one-stop shopping for this kind of display work. So, I'll agree with the trademark catchphrase, but with the catch that it's usually better to not do analytics inside SQL itself if you're also returning lower details of the data in the same call(s).

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 19, 2012 at 12:23 am

    #1502586

    Thank you budddy thats awesome it worked

  • June 19, 2012 at 2:34 am

    #1502620

    One more option that might be good for you is to use the compute clause. This will create 2 separate sets of data (one with the real data and one with the total), so many times it can't be used:

    select * from NameTable

    compute sum(Value)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 19, 2012 at 11:57 am

    #1503028

    Adi Cohn-120898 (6/19/2012)

    One more option that might be good for you is to use the compute clause. This will create 2 separate sets of data (one with the real data and one with the total), so many times it can't be used:

    select * from NameTable

    compute sum(Value)

    Adi

    The COMPUTE clause is going away.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 15 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy