how to dispaly subtotals
-
i have table like
worktype lines
A 1
A 2
b 1
b 2
c 1
c 2
can i get result like
worktype lines
A 1
A 2
total 3
b 1
b 2
total 3
c 1
c 2
total 3
please advice
-
this article may help you
http://www.databasejournal.com/features/mssql/using-the-rollup-cube-and-grouping-sets-operators.html
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
If I have two columns..I m not able to get the desired result in rollup
-
dastagiri16 (12/19/2015)
If I have two columns..I m not able to get the desired result in rollupsuggest you provide some sample data and expected results
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
seems you asked a very similar question here
http://www.sqlservercentral.com/Forums/Topic1515448-392-1.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
In my first post I have given sample data..
I have two columns like worktype and lines.
In that AA.b.c are worktypes and 123 are lines
And each worktypes I need total lines
-
In my first post I have given sample data..
I have two columns like worktype and lines.
In that AA.b.c are worktypes and 123 are lines
-
Just because you CAN do something in SQL doesn't mean you SHOULD do it in SQL.
This is typically considered display functionality and should be done in application tier not in the database... So, I'll put this in the "be careful what you wish for category"...
SELECT
worktype = COALESCE(x.total, x.worktype),
x.lines
FROM (
SELECT
total = CAST(NULL AS VARCHAR(5)),
t.worktype,
t.lines
FROM
#temp t
UNION ALL
SELECT
total = 'total',
t.worktype,
SUM(t.lines)
FROM
#temp t
GROUP BY
t.worktype
) x
ORDER BY
x.worktype,
x.total
;
output...
worktype lines
-------- -----------
A 1
A 2
total 3
b 1
b 2
total 3
c 1
c 2
total 3
-
dastagiri16 (12/19/2015)
In my first post I have given sample data..But not as readily consumable data. In order to demonstrate the answer using code, we'd have to create a table and then convert you posted data to inserts to populate the table. Please see the first link in my signature line below under "Helpful Links" on how to get better help much more quickly.
--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) -
Jason A. Long (12/20/2015)
Just because you CAN do something in SQL doesn't mean you SHOULD do it in SQL.Heh... yeah... so where would you do it? SSRS? Front End? PowerShell? There's no reason not to do this in T-SQL especially if there's a ton of data that needs to be aggregated. It's much kinder to the pipe and the server itself to send the aggregated data rather than sending it all to be aggregated.
--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) -
THANKS A LOT JASON....HAVE A NICE DAY
-
Ok, first, the following is how you should post data for your requests. Yes, I'll admit that your example is quite easy but people (like me) don't like to waste time posting a possible solution that might not actually work. We do like to test. Anything that you can do to make such testing easier (like providing readily consumable data) will mean that folks will jump on your post a lot sooner instead of putting it off until later (like I did).
Yes, it only takes a minute or two but you would only have to do it once. A lot of folks work dozens of posts a day and just don't have the time to spend that extra minute or two and will always gravitate towards those posts where someone has saved us that minute or two. 😉
I also changed the values to make sure we didn't have simple repeats occurring and to see what happens when we have some other number of details other than "2" for each WorkType.
--===== If it exists, drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create the Test Table
CREATE TABLE #TestTable
(
WorkType CHAR(1)
,Lines INT
)
;
--===== Populate the Test Table
INSERT INTO #TestTable
(WorkType, Lines)
SELECT 'A', 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'B', 3 UNION ALL
SELECT 'B', 4 UNION ALL
SELECT 'C', 5 UNION ALL
SELECT 'C', 6 UNION ALL
SELECT 'C', 7 UNION ALL
SELECT 'D', 8
;
Now that we have some test data to mess around with, we can start to demonstrate for understanding.
[font="Arial Black"]Step 1: Perform the "Business Logic"[/font]
As with any good code, you must separate the "Business Layer" from the "Presentation Layer" regardless of where either or both are accomplished.
With that in mind, let's solve the "Business Logic" in the simplest manner possible...
SELECT WorkType
,Lines
,TotalLines = SUM(Lines)
FROM #TestTable
GROUP BY WorkType, Lines WITH ROLLUP
;
That does, in fact, actually produce the correct answers as well as solving for a "missing requirement", which I'm sure that will come as an "added scope" request from your users. It not only produces sub-totals, but it also solves for the grand-total.
WorkType Lines TotalLines
-------- ----------- -----------
A 1 1
A 2 2
A NULL 3
B 3 3
B 4 4
B NULL 7
C 5 5
C 6 6
C 7 7
C NULL 18
D 8 8
D NULL 8
NULL NULL 36
[font="Arial Black"]Step 2: Exploring the Hidden Logic of Rollup[/font]
What most articles on ROLLUP, CUBE, and GROUPING SETS never explain is the hidden "GROUPING" logic that's available when you use any of those. Yes, most people will tell you to do display formatting in the GUI or in a reporting tool such as SSRS but it's sometimes easier (you don't have to write any loops, for example) to just go ahead and do it in T-SQL. For heavy aggregations, it's also much more kind to the pipe and the server itself to at least do the aggregation in T-SQL. If you going to do that anyway, you might as well produce the sub-totals and totals.
So, here's some code that demonstrates the GROUPING function that's available whenever you use a ROLLUP, CUBE, or GROUPING SETs.
SELECT WorkType
,Lines
,TotalLines = SUM(Lines)
,WorkTypeGroup = GROUPING(WorkType)
,LinesGroup = GROUPING(Lines)
FROM #TestTable
GROUP BY WorkType, Lines WITH ROLLUP
;
And that produces the following output. Notice the WorkTypeGroup and the LineGroup columns we've created...
WorkType Lines TotalLines WorkTypeGroup LinesGroup
-------- ----------- ----------- ------------- ----------
A 1 1 0 0
A 2 2 0 0
A NULL 3 0 1
B 3 3 0 0
B 4 4 0 0
B NULL 7 0 1
C 5 5 0 0
C 6 6 0 0
C 7 7 0 0
C NULL 18 0 1
D 8 8 0 0
D NULL 8 0 1
NULL NULL 36 1 1
"Detail" lines have "0" for both group columns.
"SubTotal" lines have a "1" in the LineGroup column.
"GrandTotal" lines have a "1" in both group columns (you should see what you get with CUBE instead of ROLLUP).
Since that appears to be a "binary" pattern, let's treat it as one to help with sorting the final display and to control our "Presentation Layer" logic a bit more easily. Notice the "LineType" column is the result of Binary Addition of the two previous grouping columns.
SELECT WorkType
,Lines
,TotalLines = SUM(Lines)
,LineType = GROUPING(WorkType) * 2 + GROUPING(Lines)
FROM #TestTable
GROUP BY WorkType, Lines WITH ROLLUP
;
And that produces the following...
WorkType Lines TotalLines LineType
-------- ----------- ----------- -----------
A 1 1 0
A 2 2 0
A NULL 3 1
B 3 3 0
B 4 4 0
B NULL 7 1
C 5 5 0
C 6 6 0
C 7 7 0
C NULL 18 1
D 8 8 0
D NULL 8 1
NULL NULL 36 3
Notice the following in the above...
"Detail" lines have a value of "0"
"SubTotal" lines have a value of "1"
"GrandTotal" lines have a value of "3"
Notice that a sort by WorkType and LineType now becomes obvious and will eventually guarantee the order of display when we get to the "Presentation" layer, wherever it is.
There is no "2" because we didn't use CUBE, which would produce some additional totals that we don't want or need for this particular problem.
Obviously, we don't want the extra columns and, just as obviously, we need to replace all those NULLs with something more appropriate. Time for some "Presentation Layer" fun and we're going to do it in T-SQL.
And, YES, it's incredibly important to specify the 2 part naming in the ORDER BY or you'll end up sorting based on the result set instead of the data from the CTE.
WITH cteAggregate AS
( --=== Aggregate the data first
SELECT WorkType
,Lines
,TotalLines = SUM(Lines)
,LineType = GROUPING(WorkType) * 2 + GROUPING(Lines)
FROM #TestTable
GROUP BY WorkType, Lines WITH ROLLUP
) --=== Then display it and guarantee the sort as we want it
SELECT WorkType = CASE
WHEN LineType = 0 THEN WorkType
WHEN LineType = 1 THEN 'Sub-Total ' + WorkType
WHEN LineType = 3 THEN 'Grand-Total'
END
,Lines = CASE
WHEN LineType = 0 THEN Lines
ELSE TotalLines
END
FROM cteAggregate agg
ORDER BY ISNULL(agg.WorkType,CHAR(255)),agg.LineType
What we did was to encapsulate our business logic in a non-Recursive CTE and then just reformat what we already know in the external SELECT. Since the Grand Total line ends up having a NULL for the WorkType, we just assign the largest ASCII value to replace the NULL to keep the Grand Total from sorting to the top. You could do this using the raw grouping columns that we started with but it's a lot easier and, perhaps, more obvious to do it this way. Here's what we end up with for the final result. Notice that we are easily able to control what we actually put into the WorkType column for sub total and grand total naming.
WorkType Lines
----------- -----------
A 1
A 2
Sub-Total A 3
B 3
B 4
Sub-Total B 7
C 5
C 6
C 7
Sub-Total C 18
D 8
Sub-Total D 8
Grand-Total 36
Don't let this be the end of it. T-SQL has some amazing features in it if you look for them. Please see the following links for more on WITH ROLLUP, WITH CUBE, and GROUPING SETS.
https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
The following URL provides and ever so brief introduction to the GROUPING() function. It is a shame, though, that like so many articles on the subject (I smell a new one in the works :-D), they give no examples for how to exploit it. As you've just seen, it's VERY exploitable and useful.
https://technet.microsoft.com/en-us/library/ms178544(v=sql.105).aspx
--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) -
As a bit of a sidebar, Jason's good code requires two full scans of the underlying table... one for the Detail rows and 1 for the Sub-Total rows. A third scan would be required for a Grand-Total. GROUP BY with ROLLUP only requires 1, does not require a UNION ALL, and still produces the Grand-Total.
--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) -
J Livingston SQL (12/19/2015)
seems you asked a very similar question herehttp://www.sqlservercentral.com/Forums/Topic1515448-392-1.aspx
Ah crud. I wish I had read that and visited that post before spending time explaining on this post. Hopefully, it'll sink in this time for the OP or, maybe, help someone else trying to do the same thing.
--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) -
Jeff Moden (12/20/2015)
Jason A. Long (12/20/2015)
Just because you CAN do something in SQL doesn't mean you SHOULD do it in SQL.Heh... yeah... so where would you do it? SSRS? Front End? PowerShell? There's no reason not to do this in T-SQL especially if there's a ton of data that needs to be aggregated. It's much kinder to the pipe and the server itself to send the aggregated data rather than sending it all to be aggregated.
If all you want are the rolled up subtotals, I agree 100%. In this case, however, the op wants to keep the details plus have the subtotals injected into them... If the goal is to view the the raw data in in SSMS and go no further with it than that, then this is a useful solution. It's just that it's been my experience that the SSMS Results pane isn't a data set's final destination. I don't think it really matters whether or not that is SSRS, Crystal Reports, Excel PowerPivot or a .NET application.
So... Assuming that there will be a display tier other than the SSMS Results pane, this poses several real life problems:
1) You end up putting more data than required through the pipe than what's necessary.
2) You take an extra (unnecessary) pass at the data table(s)... (At least I'm not envisioning a way to do this without taking an extra pass... I wouldn't put it past you to prove me wrong on this point.)
3) You cripple the front-end applications ability to do it's job. With the subtotal data injected into the detail data, the front end app can't do any further aggregations because "some of the rows aren't like the others".
Plus, on the theoretical side, you can't even call it a result set... Because it no longer meets the definition of a set.
That said, I don't see where the OP stated the final purpose for the requested query. Which means that my concerns may be unfounded based on his particular application.
Edit: See point 2)... How'd I know that was going to happen? :Whistling:
(note to self... read to the end of the thread BEFORE posting...)
-
Absolutely agreed on all points except for #2 (heh... you had to know :-D).
On point #1, I absolutely agree that because the OP wants both the raw data and the aggregations, you're sending more data than you would if you just sent the raw data. Of course, that's not always the case so I'll submit that we're both right because "It Depends".
On point #3, I'll have to say I don't know whether to agree or not but will admit that "It Depends". If the data and totals were made available from a View (for example), it's pretty easy to determine which type of row you have especially if you include the "LineType" we built.
As for SSRS and Crystal reports... I'll leave that to the BI folks that need to have things "purdy" (although I have been known to gen HTML in T-SQL for my emailed morning reports because I loath both SSRS and Crystal reports and happily haven't touched managed code since 2002 :-)).
As for not actually being a result set, I agree to disagree. There's nothing preventing all of the subsets from being a part of a larger set. Each set is individually selectable, could be selected as a Union even with disparate sets (not shown in the diagram), as are all of the sets as a whole.
--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 19 total)
You must be logged in to reply to this topic. Login to reply