November 19, 2013 at 1:12 am
hi,
I have a table class
classname section Marks
first a 800
first a 200
first b 100
first b 200
second a 400
second b 400
first a 1000
i want to disply class wise section total marks... LIKE the below format....
classname section Marks
first a 800
first a 200
first a 1000
TOTAL2000
first b 100
first b 200
TOTAL300
second a 400
TOTAL700
second b 400
TOTAL1100
GRANDTOTAL5100
November 19, 2013 at 1:41 am
Take a look at GROUP BY ROLLUP.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2013 at 6:51 am
hi,
i need the text like "TOTAL" ...rollup wont give it...
Please advice
November 19, 2013 at 6:54 am
dastagiri16 (11/19/2013)
hi,i need the text like "TOTAL" ...rollup wont give it...
Please advice
You'll need to add it yourself. SQL Server is not a magic box where everything rolls out exactly how you need it.
You need to write a query that will do what you want.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2013 at 8:52 am
dastagiri16 (11/19/2013)
hi,i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
November 19, 2013 at 11:18 am
first a 800
first a 200
first a 1000
TOTAL 2000
first b 100
first b 200
TOTAL 300
second a 400
TOTAL 700
second b 400
TOTAL 1100
GRANDTOTAL 5100
I don't understand the totaling logic you want.
"TOTAL 700"?? Is that "first b" and "second a" combined??
"GRANDTOTAL 5100"??? Huh? All the original detail amounts combined don't add up to nearly that much.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 19, 2013 at 11:36 am
dastagiri16 (11/19/2013)
hi,I have a table class
classname section Marks
-------------------------------------------
first a 800
first a 200
first b 100
first b 200
second a 400
second b 400
first a 1000
i want to disply class wise section total marks... LIKE the below format....
classname section Marks
first a 800
first a 200
first a 1000
TOTAL2000
first b 100
first b 200
TOTAL300
second a 400
TOTAL700
second b 400
TOTAL1100
GRANDTOTAL5100
I suspect that there is an issue with your sample resultset; if this is what you are looking for:
classnamesectionmarks
firsta200
firsta800
firsta1000
TOTAL2000
firstb100
firstb200
TOTAL300
seconda400
TOTAL400
secondb400
TOTAL400
GRANT TOTAL3100
With this sample data:
DECLARE @marks-2 TABLE (classname varchar(10), section CHAR(1), Marksint);
INSERT @marks-2 VALUES
('first','a', 800),
('first','a', 200),
('first','b', 100),
('first','b', 200),
('second','a', 400),
('second','b', 400),
('first','a', 1000);
You can do this:
WITH x(classname, section, t1, t2, rn) AS
(
SELECT classname
,section
,SUM(Marks)
,SUM(Marks) OVER (PARTITION BY classname,section)
,ROW_NUMBER() OVER (PARTITION BY classname,section ORDER BY (SELECT NULL))
FROM @marks-2
GROUP BY ROLLUP(classname, section, marks)
)
SELECTCASE WHEN t1=t2 AND rn>1 THEN 'TOTAL' ELSE classname END classname,
CASE WHEN t1=t2 AND rn>1 THEN '' ELSE section END AS section,
t1 AS marks
FROM x
WHERE section is not null AND classname is not null
UNION ALL
SELECT TOP 1'GRANT TOTAL', '', SUM(t1)
FROM x
WHERE t1=t2 AND rn>1
There is certainly a better solution out there using CUBE or GROUPING SETS but this was what I could put together quickly...
You can do it using ROLLUP or GROUPING SETS like so:
-- GROUPING SETS SOLUTION
SELECTISNULL(classname, 'GRAND TOTAL'),
section,
SUM(marks)
FROM dbo.class
GROUP BY
GROUPING SETS
(
(classname, section, marks),
(classname, section),
()
);
-- ROLLUP SOLUTION
WITH rollup_unfiltered AS
(SELECTISNULL(classname, 'GRAND TOTAL') AS classname,
section,
SUM(marks) AS marks
FROM dbo.class
GROUP BY ROLLUP(classname, section, marks))
SELECT * FROM rollup_unfiltered
WHERE section IS NOT NULL OR (section IS NULL AND LEFT(classname,1)='G');
Either of these will get you what you need except for the "TOTAL" for the classname,section rollup. To get the "TOTAL" the solution is not quite as elegant but this will certainly do the trick:
WITH rollup_unfiltered AS
(SELECTISNULL(classname, 'GRAND TOTAL') AS classname,
section,
SUM(marks) AS marks
FROM dbo.class
GROUP BY ROLLUP(classname, section, marks))
SELECT
CASE
WHEN marks*2=SUM(marks) OVER (PARTITION BY classname, section)
THEN 'Total' ELSE classname
END AS classname,
Section,
marks
FROM rollup_unfiltered
WHERE section IS NOT NULL OR (section IS NULL AND LEFT(classname,2)='Gr')
ORDER BY REPLACE(LEFT(classname,2),'Gr','zz')
Edit: added new code
-- Itzik Ben-Gan 2001
November 19, 2013 at 1:10 pm
clayman (11/19/2013)
dastagiri16 (11/19/2013)
hi,i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
BWAAAA-HAAAA!!!! Sure it's easy to get to the moon. All you have to do is build a rocketship! 😉
Since the OP is a self-admitted newbie at SQL Server, please post detailed instructions on how to accomplish this task using SSRS. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2013 at 7:46 pm
If you're fairly new to SQL, I would do this in SSRS, because you can get SSRS to do pretty much all the hard work for you (sorting, grouping, subtotals, totals...)
I did it by
1. creating a new report
2. adding a table to my report surface
3. creating a data source pointing to TEMPDB (you would point to the database that contains your data... I'm just using a UNION query based on the data provided.)
4. creating a dataset with the data provided.
Mine looks something like this:
SELECT 'first' AS Grp1, 'a' AS Grp2, 800 AS 'Value'
UNION ALL
SELECT 'first', 'a', 200
UNION ALL
SELECT 'first', 'b', 100
UNION ALL
SELECT 'first', 'b',200
UNION ALL
SELECT 'second', 'a', 400
UNION ALL
SELECT 'second', 'b', 400
(Aside: Is it possible to upload RDL files? Looks like not...)
Basically, I added Marks to the tablix, and deleted the other columns.
Then add a Row Group by Section.
Then add a second Row Group by ClassName.
add footers for both.
In the Footers, add SUM(Marks).
I added my groups above left, but you could do adjacent left...
November 19, 2013 at 9:56 pm
pietlinden (11/19/2013)
If you're fairly new to SQL, I would do this in SSRS, because you can get SSRS to do pretty much all the hard work for you (sorting, grouping, subtotals, totals...)(Aside: Is it possible to upload RDL files? Looks like not...)
You can upload RDL files at txt or just paste the code in a code tag. 😉
-- Itzik Ben-Gan 2001
November 20, 2013 at 2:23 am
Uncharted territory... so here goes... I attached the RDL as a TXT file. (So just change the extension back to RDL and you should be able to import it into SSRS.)
The Excel file shows what the output looks like in the Report. (The formatting isn't great, but that wasn't really my main concern.)
Maybe I said this already... I just used a UNION query to create the dataset. You can use TEMPDB as a data source and it works just fine.
Hope this helps!
(Interesting post! Learned a few things!)
Pieter
November 20, 2013 at 10:42 am
Jeff Moden (11/19/2013)
clayman (11/19/2013)
dastagiri16 (11/19/2013)
hi,i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
BWAAAA-HAAAA!!!! Sure it's easy to get to the moon. All you have to do is build a rocketship! 😉
Since the OP is a self-admitted newbie at SQL Server, please post detailed instructions on how to accomplish this task using SSRS. :w00t:
Personally I would have done this in SSRS because I know creating a row group or two is fairly easy. Also, a lot more flexible than the t-sql solution for this particular requirement. And no, groups in SSRS are not rocket science at all. Detailed instructions to create a row group? Are you kidding me? :w00t:
November 20, 2013 at 12:13 pm
clayman (11/20/2013)
Jeff Moden (11/19/2013)
clayman (11/19/2013)
dastagiri16 (11/19/2013)
hi,i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
BWAAAA-HAAAA!!!! Sure it's easy to get to the moon. All you have to do is build a rocketship! 😉
Since the OP is a self-admitted newbie at SQL Server, please post detailed instructions on how to accomplish this task using SSRS. :w00t:
Personally I would have done this in SSRS because I know creating a row group or two is fairly easy. Also, a lot more flexible than the t-sql solution for this particular requirement.
I agree that this is something that can be done quite easily using SSRS. I work on SSRS every day and allowing the application perform the sorting and grouping is one way to dramatically speed up T-SQL queries. If I were using the data in the OP to create an SSRS report I would certainly allow the application to do the sorting, especially if there is any filtering done inside the report.
That said, this is a T-SQL forum which is why the OP question was not asked in the SSRS forum.
And no, groups in SSRS are not rocket science at all. Detailed instructions to create a row group? Are you kidding me? :w00t:
It's not rocket science in t-sql either. Here's my detailed instructions for accomplishing this using t-sql:
1. Read Microsoft SQL Server 2012 T-SQL Fundimentals by Itzek Ben-Gan (page 234)*
2. Do this**:
SELECTISNULL(classname, 'GRAND TOTAL'),
section,
SUM(marks)
FROM dbo.class
GROUP BY
GROUPING SETS
(
(classname, section, marks),
(classname, section),
()
);
3. Look at the linear query execution plan and smile (optional) :smooooth:
* The solution works for 2008+
** The "TOTAL" requirement excluded for brevity
-- Itzik Ben-Gan 2001
November 20, 2013 at 2:14 pm
clayman (11/20/2013)
Personally I would have done this in SSRS because I know creating a row group or two is fairly easy. Also, a lot more flexible than the t-sql solution for this particular requirement. And no, groups in SSRS are not rocket science at all. Detailed instructions to create a row group? Are you kidding me? :w00t:
No, I wouldn't kid you. Someone who doesn't know how to do it, like the OP, would need detailed instructions. Since, according to you and several others, it's not a difficult thing, then you should have no problem with providing the detailed instructions. Your original answer just struck me as being a bit short. It would be like me saying "Do it in T-SQL" and providing no details.
I'm also curious what would be "more flexible" is SSRS for this sort of thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2013 at 2:22 pm
pietlinden (11/20/2013)
Uncharted territory... so here goes... I attached the RDL as a TXT file. (So just change the extension back to RDL and you should be able to import it into SSRS.)The Excel file shows what the output looks like in the Report. (The formatting isn't great, but that wasn't really my main concern.)
Maybe I said this already... I just used a UNION query to create the dataset. You can use TEMPDB as a data source and it works just fine.
Hope this helps!
(Interesting post! Learned a few things!)
Pieter
It HAS been interesting. Looking at that RDL file, I can see why it takes so long to render seemingly simple reports. That's a whole lot of I/O overhead for such a simple thing. It's amazing that MS didn't take more of a CSS like stance on these things.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply