Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subtotals & Grand Totals

Author  Topic 

charrbrad
Starting Member

3 Posts

Posted - 2007-02-28 : 09:50:24
I am new to SQL and have been given the task of adding Subtotals and a Grand Total to a report. Below is my code...can someone point me in the right direction?

SELECT
POP30300.POPRCTNM,
receiptdate,
VENDORID,
VENDNAME,
POP30310.ITEMNMBR,
SERLTNUM LOTNUMBR,
LandedCost = ISNULL(CONVERT(money,LOTATRB1),0),
STNDCOST = ISNULL(CONVERT(money,STNDCOST),0),
LandedCostStatus = CASE WHEN CONVERT(money,ISNULL(LOTATRB1,0)) = 0 THEN 'Missing' ELSE
CASE WHEN (CONVERT(money,ISNULL(LOTATRB1,0))-STNDCOST)/STNDCOST*100 NOT BETWEEN -30 AND 30 THEN 'OutOfRange' ELSE 'Okay' END END,
PONUMBER,
ISNULL(POP30310.UNITCOST,0) / ISNULL(POP30310.UMQTYINB,1) POPRICE,
ISNULL(QTYAVAIL,0) QTYAVAIL
FROM ODB.dbo.POP30300 POP30300 (NOLOCK)
INNER JOIN ODB.dbo.POP30310 POP30310 (NOLOCK) ON POP30300.POPRCTNM = POP30310.POPRCTNM
INNER JOIN ODB.dbo.POP30330 POP30330 (NOLOCK) ON POP30310.POPRCTNM = POP30330.POPRCTNM AND POP30310.RCPTLNNM = POP30330.RCPTLNNM
INNER JOIN ODB.dbo.IV00301 IV00301 (NOLOCK) ON POP30330.ITEMNMBR = IV00301.ITEMNMBR AND POP30330.SERLTNUM = IV00301.LOTNUMBR
LEFT JOIN ODB.dbo.IV00101 IV00101 (NOLOCK) on POP30330.ITEMNMBR = IV00101.ITEMNMBR
LEFT JOIN (SELECT ITEMNMBR, LOTNUMBR, SUM(QTYRECVD-QTYSOLD-ATYALLOC) QTYAVAIL
FROM ODB.dbo.IV00300 IV00300 (NOLOCK)
GROUP BY ITEMNMBR, LOTNUMBR) QTYAVAIL
ON POP30330.ITEMNMBR = QTYAVAIL.ITEMNMBR AND POP30330.SERLTNUM = QTYAVAIL.LOTNUMBR
WHERE POP30300.POPTYPE IN (1,3)
AND POP30300.VOIDSTTS = 0
-- AND POP30300.receiptdate > DATEADD(dd,-35,GETDATE())
AND ISNUMERIC(LOTATRB1) = 1
AND ISNULL(QTYAVAIL,0) <> 0








SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 10:02:54
WITH CUBE
WITH ROLLUP


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 10:10:53
Which alias did you have before?
I have seen these table names here at SQLTeam in the past...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

charrbrad
Starting Member

3 Posts

Posted - 2007-02-28 : 10:12:47
This is my first time on this forum....I found it through a Google search
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-28 : 11:02:38
The totals and grand totals should be calculated ON YOUR REPORT, not using T-SQL.

What reporting tool are you using to present this report? Reporting Services? Crystal? Access? Something else? They all have very easy to use grouping, totaling and subtotaling features.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

charrbrad
Starting Member

3 Posts

Posted - 2007-02-28 : 11:07:58
We are us ASP
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-28 : 11:25:45
Then there you go. Since you are generating the report by hand using ASP, you are going to need to write code to handle grouping to output subtotals and all that, and you are already looping through the rows to create the HTML, so just use a couple of variables to accumulate totals and keep track of the current group and there you have it.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Stevo
Starting Member

8 Posts

Posted - 2007-02-28 : 12:22:22
Actually, you can do all the processing in SQL Server (my preferred method) and tell the ASP code to look for a key value and change the presentation of the values.

+Stevo
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-28 : 12:33:48
quote:
Originally posted by Stevo

Actually, you can do all the processing in SQL Server (my preferred method) and tell the ASP code to look for a key value and change the presentation of the values.

+Stevo



that is correct, you can, if you want to over-complicate things. Why change BOTH the T-SQL and the ASP code when all you need to do is make minor tweaks to the ASP code? How would you change his T-SQL code to do the totaling and subtotaling?



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Stevo
Starting Member

8 Posts

Posted - 2007-03-02 : 14:11:32
Easily. Run the processing in a while loop if necessary into a secondary table such as table var or additional temp/global temp table if necessary, then select * from that table.

Or run the params in a while loop into udf's and dump the procedures that way.
Just my preference to keep the logic in SQL Server so it's the same everywhere.

+Stevo
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-02 : 14:39:11
quote:
Originally posted by Stevo

Easily. Run the processing in a while loop if necessary into a secondary table such as table var or additional temp/global temp table if necessary, then select * from that table.

Or run the params in a while loop into udf's and dump the procedures that way.
Just my preference to keep the logic in SQL Server so it's the same everywhere.

+Stevo



Well, I guess I am still waiting for your simple code to do this T-SQL.

Here's the code in asp:


var total = 0

do while not rs.eof()

... your output here ....
total = total + rs("value")

rs.movenext()
loop

.. output total here


In about 3 lines of code, with absolutely no additional processing time, temp tables, loops or anything else, we have done totals in ASP. subtotals are just as easy, we just need to keep track of the current value of each key and output/reset totals each time.

It is a huge waste of your programming time and SQLs processing resources to force this into T-SQL when T-SQL is not designed as a totalling, subtotalling language; recordsets returned by a relational database are not designed to include group totals and row totals, that is typically not the job of the RDBMS, but rather the presentation layer. Obviously, the same applies to formatting and indenting and suppressing certain columns and the like -- these are presentation functions.

If you need to store these totals and subtotals in the database, or use them for further processing within T-SQL (i.e, to get totals per group so you can calculate percentages of that total) then you can make a case for doing the work in T-SQL; otherwise, it is much shorter, quicker and easier to let your presentation layer do its job and let the database do its job.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 14:49:02
WITH ROLLUP or WITH CUBE also does subtotals on database side...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-02 : 16:09:54
that's true, and if you need these totals in T-SQL, they are useful, but you then have to deal with sorting the NULLS at the end of each group (the opposite of the natural sort for nulls), it is a less efficient than without, and you still have to do some formatting at the front end. Also, if any of your grouping values have any NULLs in them, you get kind of weird results (though using the grouping() function does help):

declare @t table (grp char(1), val int)

insert into @t
select null, 1 union all
select 'A', 2 union all
select 'A', 3 union all
select 'B', 4 union all
select 'B', 5

select grp, sum(val), grouping(grp)
from @t
group by grp
with rollup

Overall, I do like ROLLUP but I tend to recommend that, if you can, do your totaling at the presentation layer.


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-03-04 : 14:49:34
Nah... I agree with Peter... do it in SQL... it's just as easy or maybe even easier to do there... you're already doing the GROUP BY so why not use it?

declare @t table (grp char(1), val int)

insert into @t
select null, 1 union all
select 'A', 2 union all
select 'A', 3 union all
select 'B', 4 union all
select 'B', 5

SELECT Grp = CASE
WHEN GROUPING(grp) = 0
THEN grp
ELSE 'Grand Total'
END,
GroupTotal = SUM(val)
FROM @t
GROUP BY grp WITH ROLLUP



--Jeff Moden
Go to Top of Page

Stevo
Starting Member

8 Posts

Posted - 2007-03-05 : 11:08:01
That's the point. In Insurance companies, Finance, etc programmers often slip up because they are processing key metrics at different points, sometimes differently.

+Stevo
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-05 : 13:27:16
I come from a Finance reporting background, and we always did totals, subtotals, and groupings on the reports themselves. A DBA/Database programmer is probably *more* likely to screw up doing totals in T-SQL rather than someone writing a report using a tool that is *designed* to handle subtotaling. Calculating key metrics is one thing, outputting grand totals or subtotals or groups using a set of data is another.

Also -- if you are not doing any grouping on your SQL statement, i.e., if you are returning detailed rows on a report, then using ROLLUP or CUBE doesn't do you any good since it doesn't work. You could UNION your detailed results with your subtotal/totals results to do it all in T-SQL in that case, but then you just made your database code twice as slow and twice as long as it needs to be, and this can be drastic when a report takes a considerable amount of time to run. If you decide to use temp tables instead, you may get it to be only a little less efficient but now your simple SELECT statement is a complicated, multiple-step process that leaves much room open for error in T-SQL code. In either case, you are now forcing specific totals and grouping to your presentation layer which can no longer just use standard client calculations on the recordset since the data now includes totals and subtotals. (i.e., the client can no longer add up a column in the data and consider it a total, since that column already includes subtotals and grand totals potentially).

Since there is no standard way to alert your client that certain rows of data are "special" total and subtotal rows, different clients may interpret the results differently, and certainly standard report writing tools will not be able to process the data either. Now, you have to handle totalling on *both* sides -- database AND client -- to ensure that you don't double-count results or output a "totals" row in the same font or format as a details row, and so on.

All in all, I find it hard to make a good case for outputting totals in your data when it is so easy for a client tool to do it, and so much more flexible and consistent as well. It just doesn't make any sense except for in extremely simple, rare cases.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-03-05 : 23:56:05
quote:
Originally posted by jsmith8858

I come from a Finance reporting background, and we always did totals, subtotals, and groupings on the reports themselves. A DBA/Database programmer is probably *more* likely to screw up doing totals in T-SQL rather than someone writing a report using a tool that is *designed* to handle subtotaling. Calculating key metrics is one thing, outputting grand totals or subtotals or groups using a set of data is another.



Heh... I guess I should be insulted, and I am... I do have to ask... what makes you think some GUI programmer is going to do any better than someone who actually understands the data behind the scenes especially if the database person also has a financial background?

And "GROUPING" is a tool specifically designed to "handle "totals and subtotals.

quote:
Also -- if you are not doing any grouping on your SQL statement, i.e., if you are returning detailed rows on a report, then using ROLLUP or CUBE doesn't do you any good since it doesn't work.
You could UNION your detailed results with your subtotal/totals results to do it all in T-SQL in that case, but then you just made your database code twice as slow and twice as long as it needs to be, and this can be drastic when a report takes a considerable amount of time to run. If you decide to use temp tables instead, you may get it to be only a little less efficient but now your simple SELECT statement is a complicated, multiple-step process that leaves much room open for error in T-SQL code. In either case, you are now forcing specific totals and grouping to your presentation layer which can no longer just use standard client calculations on the recordset since the data now includes totals and subtotals. (i.e., the client can no longer add up a column in the data and consider it a total, since that column already includes subtotals and grand totals potentially).



Really?
DECLARE @t TABLE (RowNum INT IDENTITY(1,1),Grp CHAR(1), Val INT)

INSERT INTO @t
SELECT NULL, 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'B', 4 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'B', 5

SELECT d.Grp,d.GroupTotal
FROM (
SELECT RowNum,
Grp = CASE
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 0 THEN Grp
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 1 THEN 'Sub Total ' + ISNULL(grp,'NULL')
ELSE 'Grand Total'
END,
GroupTotal = SUM(Val)
FROM @t
GROUP BY Grp,Val,RowNum WITH ROLLUP
) d
WHERE RowNum > 0 OR Grp LIKE '%Total%'


quote:

Since there is no standard way to alert your client that certain rows of data are "special" total and subtotal rows, different clients may interpret the results differently, and certainly standard report writing tools will not be able to process the data either. Now, you have to handle totalling on *both* sides -- database AND client -- to ensure that you don't double-count results or output a "totals" row in the same font or format as a details row, and so on.

All in all, I find it hard to make a good case for outputting totals in your data when it is so easy for a client tool to do it, and so much more flexible and consistent as well. It just doesn't make any sense except for in extremely simple, rare cases.

- Jeff
http://weblogs.sqlteam.com/JeffS




I suppose the words "Sub Total" and "Grand Total" just aren't going to mean "Sub Total" and "Grand Total" to anyone, huh?

Jeff, I understand where you're trying to go with all of this but I just spent the weekend cleaning up after "Professional GUI Programmers" with "Degrees in Finance"... their month end duplicate deletion program (YES!!! THEY ALLOW DUPES TO CREEP IN!!!!) and month end reporting took 20-24 hours to complete IF it didn't deadlock or throwup a furball on the carpet. Everyone one of them is a card carrying certified friggin' genius!

After my rewrite, done 100% in SQL including the reports that they generated, the process took only 17 minutes and 34 seconds... and I gave them the extra month back of reporting the customer wanted but couldn't get because the previous "GUI" method took too long.

Another example is our IVR... the certified geniuses that designed it produced a month ending report, by day of month, and they moved the decimal point two places to the right so that 100% came out as 10,000% and the report took over a half hour to generate!

I wrote a similar report but did it by hour of the day with daily totals and it all get's done in 12 seconds flat!

And don't get me started on the report writers... guess who they come to when they can't figure something out? It's not the resident accountant, for sure!

Leave batch processing to the database... and stop bad mouthing DBA's and Database Programmers...

--Jeff Moden
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 08:22:11
Jeff -- I appreciate your response but I think you completely missed all of my points.

1) I AM a database programmer. Keep that in mind. I can't imagine why you would be offended if I suggest that a client programmer or a REPORT WRITER might be able to also correctly calcualte totals, groups and subtotals. A client programmer should be quite offended by your comments, on the other hand. This isn't a situation of discussing a "GUI guy" verses a databsae programmer -- it's a front end programmer versus a database programmer. If you have nothing but guys who can write javascript and HTML, then I guess you do need to do more work on the backend, but for a typical, standard scenario where you have actual programmers handling your data, it is an insult to them to call them "GUI guys" and assume that they have no ability to process data from a database. And for report writers, it is even worse since the tool they use is DESIGNED for that.

2) When you say "the words 'Grand Total' and 'Sub Total' will mean that things are grouped" you are completely wrong. While I might return the varchar value "Customer Total", in column X, you might use just the word "Total", in column Y. It is completely arbitrary. A human might be able to parse your rows and columns and figure out by the way you worded or sorted things which rows are supposed to be totals and therefore excluded from all the normal calculations a client always does, but that is not a standard and a reporting tool or programming class like a DataGrid certainly won't. That is exactly like saying "the column is labeled "Unit Price" so therefore the client should understand that the amount in that column is a "money" value and should be formatted as such; that is completely arbitrary and subjective.

3) To address your re-writes, that is great, but taking anyone's bad code and rewriting in any manner that is not as bad will produce better results, it doesn't mean that you did it the easiest or most efficient way.

4) Finally, looking at your great example, consider that you just recommended that we should replace a simple, efficient, and clean sql statement:

select rowNum, Grp, val
from @t
order by rowNum

with

SELECT d.Grp,d.GroupTotal
FROM (
SELECT RowNum,
Grp = CASE
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 0 THEN Grp
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 1 THEN 'Sub Total ' + ISNULL(grp,'NULL')
ELSE 'Grand Total'
END,
GroupTotal = SUM(Val)
FROM @t
GROUP BY Grp,Val,RowNum WITH ROLLUP
) d
WHERE RowNum > 0 OR Grp LIKE '%Total%'


And that's the easiest, simplest case scenario! It's longer, messier, the performance is worse, and now the GUI/report has no say in how it will format or group things or if it wants to use the words "Total" instead of "Grand Total" -- you need to change your database code to change minor formatting! What's next -- returning HTML because you don't trust the web guy ?? And how does the front-end guy know that certain rows should be indented, or bolded, and whatnot? He or she has to write code like "If columnx='Grand Total' then make line bold" -- but now their code breaks if you change your T-SQL since maybe you are not returning that exact phrase any more!

It is odd that you specifically mention report writers ... Let me ask you this: If I am a report writer using, say, reporting services, and I work with you, and we need to design a simple report that shows time sheet details with subtotals by employee, project and month, you would want to calculate the groups and total calculations in T-SQL and not using the grouping and totaling features in reporting services?? Because you don't trust that I would be able to do it? Would you realize that you just made not only your job much harder, but mine as well since now I have to invent some weird custom formatting to simulate the simple grouping and totaling features and formatting? What happens when the next guy inherits this project from us -- we have T-SQL code 5 times longer than it needs to be, about twice as slow on average, and now a report design that is a complete mess with nothing but a huge DETAILS sections and all kinds of crazy formulas which attempts to simulate the grouping and totaling features which already exist!

that is weak. that is like client programmers not using RI because they don't trust the DBA, so they will write the code themselves, or when database guys returning nothing but varchar's in all of the columns in a select because they don't trust the front-end guys to format a money or date. The solution isn't to abandon best practices and make things more complicated, longer and slower, the solution is to work together to do things right. If you can't trust a co-worker to do his or her job, it doesn't help when you overcompensate and start doing your job wrong as well.

Also -- can you explain this:
quote:

And don't get me started on the report writers... guess who they come to when they can't figure something out? It's not the resident accountant, for sure!

Leave batch processing to the database... and stop bad mouthing DBA's and Database Programmers...



If the report writers come to you for help, then help them. Show them how easy it is in Crystal or RS to do subtotals and groups. Is it easier for you to tell them "get away from me I'll do it myself in T-SQL!" ?? If that's the approach you take, that's fine, but I certainly do it very differently and the results are quite good -- everyone learns, things are clean and efficient, we use best practices, it all makes sense. The database returns the data, the presentation tool presents the data. It almost even makes sense!

As for the "batch processing" and bad mouthing ... huh?? Reports are "batch processes"?

I suggest maybe you browse around SQLTeam or read my blog a little if you think that I am bad mouthing database programmers .....

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-03-06 : 08:51:32
I agree that using reporting services is a good way to go... but not writing an app for the reports... so far as having code "about twice as slow on average", that's just not been my experience. And that's my point... everyone thinks doing stuff like this in a database is slow and weak. Not true.

And, if you don't mind, lets see the full ASP example you'd write to replace the small example I wrote which includes the subtotals and grand totals.

--Jeff Moden
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 08:58:05
I am a little rusty with asp, but you got it.

While I am working on this -- please write some ASP code for your TSQL statement as well. And don't forget, the subtotals come AFTER the groups, which your TSQL doesn't do. And let's make the subtotal italic, and the grand totals bold in ASP. Just a simple 2-column report is fine.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 09:11:24
Ok, here's mine. not tested, just written from scratch.

Keep two things in mind:

1) how short the SQL code is. it is exactly what you expect.

2) how easy it is to change the formatting. What if, just before each group, we want to output a <TR> with a <TD> spanning both columns showing the group name? Very easy. What if we want to exclude the subtotal if there is one 1 item in the group (a common request)? Very easy, just add a simple counter and an IF. Where do we change verbiage and grouping? At the ASP page -- no need to change it in BOTH the SQL and ASP code, which you would have to do otherwise if you force formatting into you T-SQL code.

This might best be illustrated with a slightly more complicated example. I can create one using northwind, if you are up for it. We'll do some group headers, group footers, some subtotals, grand totals, and so on. Of course, I don't recommend writing anything but the simplest of reports in this manner (I am glad to hear that we both agree that reporting tools should be used) but it still is relevant overall.

SQL Code:


select rowNum, Grp, val
from @t
order by grp, rownum


ASP Code:



<!-- REPORT HEADER -->

<table>
<tr>
<th>Group</th>
<th>Amount</th>
</tr>

<%
dim lastgrp = ""
dim tot = 0
dim subt = 0
dim nextgroup = ""

while not rs.eof
%>

<!-- DETAIL HERE -->

<tr>
<td><%=rs("Grp")</td>
<td><%=rs("val")</td>
</tr>

<%
subt = subt + rs("val")
tot = tot + rs("val")

rs.moveNext

if rs.EOF then
nextgrp = "*end*"
else
nextgrp = rs("Grp")
end if

if lastgrp <> nextgrp then
%>

<!-- GROUP FOOTER HERE -->

<tr>
<td><i>Sub Total <%=lastgrp%></i></td>
<td><i><%= subt %></i></td>
</tr>
<%
subt = 0
lastgrp = nextgroup
end if

loop
%>

<!-- REPORT FOOTER HERE -->

<tr>
<td><b>Grand Total</b></td>
<td><b><%= tot %></b></td>
</tr>
</table>


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
    Next Page

- Advertisement -