January 2, 2018 at 11:40 am
I want to group data like all invoices from a supplier which is easy enough but when selecting multiple suppliers, I want to have an additional row in between the grouped suppliers with the total of a column lets say total of all the invoices. Easy to do in excel but I want to have it done prior to excel so the Executives don't have to manually do it when they export the data to excel. Is this possible using TSQL?
January 2, 2018 at 1:25 pm
Yes. Use GROUPING SETS. If you want a more complete answer, post sample data and expected results as outlined in the first link in my signature.
This would be much easier in a report tool such as SSRS. Have you considered that?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2018 at 5:52 am
drew.allen - Tuesday, January 2, 2018 1:25 PMYes. Use GROUPING SETS. If you want a more complete answer, post sample data and expected results as outlined in the first link in my signature.This would be much easier in a report tool such as SSRS. Have you considered that?
Drew
It will be on a SSRS server in the end, but how the program works is when they export to excel, it uses the stored procedure to gather the data.
I looked up the Grouping sets and it appears you are correct, Never knew about them. Thanks. I will see if I can get it to work.
January 3, 2018 at 6:22 am
drew.allen - Tuesday, January 2, 2018 1:25 PMYes. Use GROUPING SETS. If you want a more complete answer, post sample data and expected results as outlined in the first link in my signature.This would be much easier in a report tool such as SSRS. Have you considered that?
Drew
OK, I am not getting the results so I will post some data.
The end result should look similar to:
fvendno fcompany POItem PORelsDate........TotStdCost
001275 B&R Industried.............................. 1.00
001275 B&R Industried.............................. 1.00 (total Row)
002119 Roberson Tool................................ 2.00
002119 Roberson Tool................................ 3.00
002119 Roberson Tool................................ 5.00 (total Row)
002302 Unique Automation.......................... 4.00 ( and so on).
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
Create table #mytable(
fvendno char(6),
fcompany char(50),
POItemKey char(12),
PORelsdate datetime,
totstdcost numeric(15,4),
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
--===== All Inserts into the IDENTITY column
Set Identity_Insert #mytable ON
--===== Insert the test data into the test table
Insert into #mytable (fvendno, fcompany, POitemKey, PORelsDate, totstdcost, ID)
Select '000027', 'Albany Steel', '123456 1', Cast('01/01/2018' as datetime), 1.00, 1 Union all
Select '000027', 'Albany Steel', '123459 1', Cast('01/01/2018' as datetime), 2.00, 2 Union all
Select '000060', 'Apple Rubber', '123455 1', Cast('01/01/2018' as datetime), 3.00, 3 Union all
Select '000060', 'Apple Rubber', '123467 1', Cast('01/01/2018' as datetime), 4.00, 4 Union all
Select '000080', 'Century Spring', '189456 1', Cast('01/01/2018' as datetime), 5.00, 5 Union all
Select '000127', 'Everett Manufacturing', '223456 1', Cast('01/01/2018' as datetime), 6.00, 6
--===== Set the identity insert back to normal
Set Identity_Insert #mytable OFF
Select * from #mytable
January 3, 2018 at 8:20 am
One way to approach this is to create the required detail and group levels individually, then union together and "order by" to get the results. Using CTEs keeps the individual sets simple and readable.
WITH totals (vendno, company, sumtotstdcost, myLevel) -- First get subtotals
AS ( SELECT fvendno
, fcompany
, SUM(totstdcost), 'subtotal'
FROM #mytable
GROUP BY fvendno, fcompany
)
, myUNION -- then union detail, subtotal, and report totals rows
AS ( SELECT fvendno
, fcompany
, POitemKey
, CONVERT(VARCHAR(50), PORelsDate) as PORelsDate
, totstdcost, ID
, 'detail' as myLevel
FROM #mytable UNION ALL
SELECT vendno
, company
, 'Subtotal'
, ''
, sumtotstdcost
, 0
, myLevel
FROM totals
UNION ALL
SELECT 'Report Total'
, ''
, ''
, ''
, SUM(totstdcost)
, 0
, 'zReportTotal'
FROM #mytable
)
-- select from union and order by to get the desired order
SELECT fvendno
, fcompany
, POitemKey
, PORelsDate
, totstdcost
FROM myUNION
ORDER BY fvendno
, myLevel
, ID
January 3, 2018 at 9:06 am
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;
This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;
Drew
Edited: Added the query for a grand total.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2018 at 5:57 am
drew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
January 4, 2018 at 7:19 am
bswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
January 4, 2018 at 7:23 am
bswhipp - Thursday, January 4, 2018 7:19 AMbswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
Is there a way to get the whole total at the end?
January 8, 2018 at 10:47 pm
This was removed by the editor as SPAM
January 9, 2018 at 8:14 am
subramaniam.chandrasekar - Monday, January 8, 2018 10:47 PMbswhipp - Thursday, January 4, 2018 7:23 AMbswhipp - Thursday, January 4, 2018 7:19 AMbswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
Is there a way to get the whole total at the end?
Please use GROUP BY Lookup / CUBE for your requirements.
I'd found these in SQL Server Central forum before,
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY ROLLUP (department,gender)
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY CUBE (department,gender)
I prefer the GROUPING SETS, because it gives you much finer control over what subtotals are produced. For instance, here it sounds like the OP wants three levels of totals: detail, subtotal by company, and grand total. The GROUPING SETS will produce exactly the three that he wants, ROLLUP would produce five levels of totals, and CUBE would produce 16(?).
I had already amended my previous post to show how to get a grand total using GROUPING SETS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 9, 2018 at 12:12 pm
drew.allen - Tuesday, January 9, 2018 8:14 AMsubramaniam.chandrasekar - Monday, January 8, 2018 10:47 PMbswhipp - Thursday, January 4, 2018 7:23 AMbswhipp - Thursday, January 4, 2018 7:19 AMbswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
Is there a way to get the whole total at the end?
Please use GROUP BY Lookup / CUBE for your requirements.
I'd found these in SQL Server Central forum before,
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY ROLLUP (department,gender)
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY CUBE (department,gender)
I prefer the GROUPING SETS, because it gives you much finer control over what subtotals are produced. For instance, here it sounds like the OP wants three levels of totals: detail, subtotal by company, and grand total. The GROUPING SETS will produce exactly the three that he wants, ROLLUP would produce five levels of totals, and CUBE would produce 16(?).
I had already amended my previous post to show how to get a grand total using GROUPING SETS.
Drew
I was out sick a few days. I will try to look at this in a day or so. I must have missed the grand total but will review. Thanks.
January 10, 2018 at 9:13 am
bswhipp - Tuesday, January 9, 2018 12:12 PMdrew.allen - Tuesday, January 9, 2018 8:14 AMsubramaniam.chandrasekar - Monday, January 8, 2018 10:47 PMbswhipp - Thursday, January 4, 2018 7:23 AMbswhipp - Thursday, January 4, 2018 7:19 AMbswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
Is there a way to get the whole total at the end?
Please use GROUP BY Lookup / CUBE for your requirements.
I'd found these in SQL Server Central forum before,
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY ROLLUP (department,gender)
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY CUBE (department,gender)
I prefer the GROUPING SETS, because it gives you much finer control over what subtotals are produced. For instance, here it sounds like the OP wants three levels of totals: detail, subtotal by company, and grand total. The GROUPING SETS will produce exactly the three that he wants, ROLLUP would produce five levels of totals, and CUBE would produce 16(?).
I had already amended my previous post to show how to get a grand total using GROUPING SETS.
Drew
I was out sick a few days. I will try to look at this in a day or so. I must have missed the grand total but will review. Thanks.
AHHHHHH I missed the () at the end. I put it in but did it incorrectly Thank you!!!!!
January 10, 2018 at 11:25 am
bswhipp - Wednesday, January 10, 2018 9:13 AMbswhipp - Tuesday, January 9, 2018 12:12 PMdrew.allen - Tuesday, January 9, 2018 8:14 AMsubramaniam.chandrasekar - Monday, January 8, 2018 10:47 PMbswhipp - Thursday, January 4, 2018 7:23 AMbswhipp - Thursday, January 4, 2018 7:19 AMbswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
Is there a way to get the whole total at the end?
Please use GROUP BY Lookup / CUBE for your requirements.
I'd found these in SQL Server Central forum before,
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY ROLLUP (department,gender)
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY CUBE (department,gender)
I prefer the GROUPING SETS, because it gives you much finer control over what subtotals are produced. For instance, here it sounds like the OP wants three levels of totals: detail, subtotal by company, and grand total. The GROUPING SETS will produce exactly the three that he wants, ROLLUP would produce five levels of totals, and CUBE would produce 16(?).
I had already amended my previous post to show how to get a grand total using GROUPING SETS.
Drew
I was out sick a few days. I will try to look at this in a day or so. I must have missed the grand total but will review. Thanks.
AHHHHHH I missed the () at the end. I put it in but did it incorrectly Thank you!!!!!
Drew, One other question Is there a way in the code to change the null values to blanks or space(1)?
January 10, 2018 at 11:39 am
bswhipp - Wednesday, January 10, 2018 11:25 AMDrew, One other question Is there a way in the code to change the null values to blanks or space(1)?
There are two potential sources for NULL values: the underlying data and NULLs from columns not involved in the current grouping level. You usually want to be able to distinguish between the two. There is a function that can be used to distinguish these. It gives an example of the situation that I'm referring to: GROUPING. You'll want to use that in conjunction with a CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply