SQL Tutorial - How to add total rows and columns to a Pivot Query

hello and welcome to my channel hope everybody's safe and well in this video we're going to be looking at adding totals to rows and columns in pivot tables now the first thing i should mention is this is probably not my preferred approach when it comes to working with data i would much rather output that data to a another tool such as power bi excel or even a front end application but i am going to show you in this video how we can add total rows and columns to our pivot tables as always we're going to start with creating a table and inserting some data and this will be available in the description should you wish to follow along so we're just going to create a simple table with an order id customer id product id and amount so we're just imagining we've got a number of customers we sell a number of products for an amount let's have a look at a starting point so here i've wrote out a very simple pivot query so to start with from our orders table we're just bringing in the customer id the product and the amount we're pivoting we're calculating the total amount for each product and then there's our that's our spreading columns and then we just have our final select and then lastly we're ordering by customer id let's execute the query and have a look at the data we're working with now the aim of this video is to add a total for customers so we're going to be ignoring a product and we want to add a total column for customer spend and then we're also going to be ignoring our customers and adding a total row for our products at the bottom of our query so our starting point is a pivot query and there are really three parts to a pivot query first there's the input which is the derived table then there's the pivot operation itself which is where we're computing the amount and the new columns and then there's the final select from that pivot query so the first option we're going to add and the simplest option is we can simply add an extra column which is just adding these column values together so let's have a look at what that would look like so if i just simply create a new column and the key part to this is we're working within the final select so i'm just going to put these on a single line just simply adding the plus in between rather than selecting them as columns so i'll just go ahead and do that and as you can see already this approach is probably more work than we want to do and we'll just give that an alias so we're going to be adding those that's going to give a customer total let's execute the query and have a look at what that looks like so we've managed to achieve what we were looking for we were looking to add in a total column here based on the customer's total spend across those products now this approach has a key benefit in that we can actually customize and control how that total is computed so perhaps there's some kind of product we have or something else we're pivoting by that's not actually included in the total so for example we didn't actually want printer included in the total or for some reason we had to customize that total we actually have full control at this point of what that total is made up of so that's one thing to consider but on the flip side of that is it does involve sort of some manual work and making sure that all columns are added uh correctly so we have to make

sure that if we are looking for that total to include everything we have to make sure that they are all added together so that can be quite cumbersome if we're working with a number of new columns now my preferred approach and this generally applies to anything i'm doing within a pivot query is always to add the data within this derived table so this is prior to the pivot operation has been performed this producing results to perform that pivot operation on so we're simply going to remove that calculation now and we're going to change our derived table now what we effectively have to do if we just have a look at the raw data so we've got our customer id our product and an amount now we need to add a total to this table based on customer id now we can't simply add a group by because we'd lose the level of detail we're looking for we can't use window function because that would add a new column so effectively we need to build onto this table by adding our own new row so we're going to add a union and then select customer id then we're going to create a new column which is going to not a new column but a new product effectively and we're going to call this customer total as the product and then we're going to go some amount and we will just alias that as amount it's going to be from orders table and we're going to group by customer id so if we have a look at the underlined derived table now we've built we'll see there's been some additional rows added and we now have a new product of customer total that will exist once per customer showing the total amount of customer spent now there is two gotchas with this approach first is to do with data types that we'll have a look at shortly so of course if i'm adding a string of customer total uh luckily i'm adding that to an existing string data type but we might have to do some manipulation in fact when we add in total row to the pivot query we'll see that how we handle that the other is rounding um so when we're applying any rounding and performing aggregations we need to make sure we do it in a consistent way to get the same results i'll probably talk more about that in some in another video once we have a derived table built we can then go and add that to our pivot query as if it were a different value for product so within our pivot query we're going to add the customer total and then again within our final select we're also going to add a customer total so let's just hide the results grid and we'll just go through that now so we've built on additional rows to our derived table this time aggregating by custom riding we'll just give this an alias as product just to be crystal clear so we've actually effectively just created um a value for product here and it's just simply a group by customer id to compute the sum of the amount once we've done that we can then add that as a product and then add it to the final select execute the query and we can see we're getting the same results so we've managed to add in a customer total so that's a way of adding in a column for totals but how about if we also wanted to build in a row for totals so for each product ignoring customer we want a row at the bottom to show the total for monitors laptops pcs etc so how would we go about that and it's really going to be a similar approach again we're going to build on a

derived table adding rows by union into a another calculation and this time we're going to group by product rather than custom writing so i'm just going to set minus 1 as customer id for now and we'll come back and apply some manipulation to that so this is where the data type problem will come in um so product would be itself because we're looking to group by product and then again some amount that's going to be from uh orders table and we can probably see one of the other downsides to this approach i'm going to be grouping by product is that we do hit the orders table multiple times within building this query so if i execute that query if we scroll down to the bottom we should see additional rows added with a customer id and we'll see some large amounts for product sales now because we've added those as a new customer id which is effectively going to be the rows in our result there is nothing we need to do within the pivot query or the final select because customer id already exists in both if i execute that query and we have a look at the results because we've set the customer id to minus one and it's an integer value it's in a standard order at the top most probably um in fact it will do because yeah we do have a an explicit order by on the query so we can see that that's actually at the top showing the total for products so we could leave it at that but customer id of minus one is perhaps not clear so how about if we was to change that to in this case it's going to be product total just hide the results so simply just change that minus one to product total if i execute that i will get the error to say that we sql server engine cannot convert product total to a data type int so this is what i was referring to earlier about the data type gotcha so how we're going to have to display these results is simply convert the customer id to a string so i'm just going to go and add in some cast of archer 20 just for simplicity the final query will be available within the description as well if i execute that now sorry just alias both of those as custom writing and if i execute that now um and scroll down to the bottom we can see we've actually got a product total at the bottom but in converting that to a string so all numbers are now strings we end up with an well and a non-numeric sort of sort so one first 10 then 100 then a thousand then 101 so that's not ideal and we can customize that sort order so first we're going to order by the length of customer id so that would allow 2 to go before 10 and then once we've done that we'll order by customer id if i execute that we can see our ordering issues been resolved we've added an customer total row and then we've also added in a product total row at the bottom so as you can see this is not easy to do we do need to apply some i would say perhaps unnatural manipulation to the data whereas if we took the result of a pivot and dropped into a tool like excel um or a client application or power bi we could even perform the pivot operation or even just use a matrix visual then it would be

what seems to me is a lot more natural than using a client tool like sql server management studio but it can be done and it's definitely worth playing around with and then it's up to you what you would like to do with that data this can simply be saved and sent on to users or actually the result produced for a front-end application to display the results really hope you have enjoyed that video like i say the finished query will be available in the description uh