SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row inserts for groups of rows (Totals for groups of like data).


Row inserts for groups of rows (Totals for groups of like data).

Author
Message
bswhipp
bswhipp
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 66
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?
drew.allen
drew.allen
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58289 Visits: 15287
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
bswhipp
bswhipp
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 66
drew.allen - Tuesday, January 2, 2018 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


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.
bswhipp
bswhipp
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 66
drew.allen - Tuesday, January 2, 2018 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


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
R. Brush
R. Brush
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 365
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


drew.allen
drew.allen
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58289 Visits: 15287
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
bswhipp
bswhipp
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 66
drew.allen - Wednesday, January 3, 2018 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.


Can't wait to try it. Hope to get to it today. Thank you in advance.
bswhipp
bswhipp
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 66
bswhipp - Thursday, January 4, 2018 5:57 AM
drew.allen - Wednesday, January 3, 2018 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.


Can't wait to try it. Hope to get to it today. Thank you in advance.

Drew, Thanks, This is awesome!!!

bswhipp
bswhipp
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 66
bswhipp - Thursday, January 4, 2018 7:19 AM
bswhipp - Thursday, January 4, 2018 5:57 AM
drew.allen - Wednesday, January 3, 2018 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.


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?
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3228 Visits: 531
bswhipp - Thursday, January 4, 2018 7:23 AM
bswhipp - Thursday, January 4, 2018 7:19 AM
bswhipp - Thursday, January 4, 2018 5:57 AM
drew.allen - Wednesday, January 3, 2018 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.


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)


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search