How to calculate Subtotals in SQL Queries

The reason for this combo usage is to add a
unique number to each row and then we will use that numbered row for grouping. Now we will use the SQL CASE statement and
GROUPING function together so that we will replace NULL values with more meaningful explanations. You create Subtotal items in QuickBooks Desktop Pro by opening the “Item List,” first. To create a new “Subtotal” item, click the “Item” button in the lower-left corner of the list window. However, we need to remember some circumstances where that may not be the case, and there are additional calculations that follow afterward.

When you want to write “subtotal,” it’s important to use one spelling and only one to do so. Learn about addition, the math operation to find the sum of two or more numbers. Discover how to perform addition, how to use place values to do addition with larger numbers, and how to use regrouping to perform addition. I’m asking which would be the correct word for the final amount on an invoice after all calculations and considerations. In this example, we will pass 3 columns into the ROLLUP extension and then this extension will generate subtotal
rows for all hierarchies.

Subtotal: the “Sub-” Prefix

However, it is also important to point out that both totals and subtotals can result in a negative amount as well. We can see this in financial and mathematical calculations, in cases where negative figures outweigh the positives. Finally, we don’t always have to use prices and costs to use “subtotals.” As we demonstrated earlier, you can also count groups of things or people in a subtotal. Now let’s see when “total” is the more acceptable word of the two in a sentence.

In this article, we’ll explain the major differences between the two so you don’t have to worry about accidentally using the wrong one. For some cases, we may require to add a subtotal in SQL query for only one column. In this circumstance, we can use
the ROW_NUMBER() and NEWID() function together to handle this issue.

What Is The Difference Between “Total,” “Subtotal,” And “Grand Total”?

For example, you have a weekly grocery budget of $300, and you spent $50 on Monday and $120 on Wednesday; this would mean your subtotal spent so far would be $170. Not every prefix has such a drastic impact on what follows, though — many have a far more subtle effect, like the prefix sub-. “Sub-” indicates that the subject is subordinate or secondary to a more important subject (source). As long as we’re working with something that can be quantified by numbers, we can use “total” to talk about the overall number of things. One really easy tip to help you remember is to remember which one is higher. The GROUPING function is used to determine whether the columns in the GROUP BY list have been aggregated.

One would be forgiven for thinking that denoting something as the total would mean that it is the final sum — that is, after all, the definition that this article has provided. Our expense subtotal would tally up all the areas where we spend our money. Of course, the goal would be to make sure the income subtotal is larger than the expenditure subtotal. It would be the answer to one section’s calculation that contributes toward the bigger picture (source). However, you may sometimes see “sub-total” written, even though it’s incorrect. It mostly depends on where you go and which version they prefer when you see either of them written down.

Final Thoughts

We traditionally use them to sum up a specific set of data relating to one category or as milestones as we calculate a final total. We commonly find subtotals on invoices, where they’ll list several items before adding items like taxes and shipping later. The GROUP BY statement is used to groups the rows that have the same values in a new summary row and it is the lead actor of the aggregate queries.

“Total” should be used when you’re talking about the absolute end sum of something. It combines all aspects of a bill together, including discounts, taxes, prices, etc. “Subtotal” should be used when talking about the total of a set of numbers that isn’t the final sum. English is not my native language, so please correct me if I’m wrong, but common sense tells me that instead of using ‘subtotal’, we should use ‘final total’ after all the calculations are done. We can see an extra row at the end of the result set, this row shows the grand total sales of the years without
considering the sales month and quarters. You can create Subtotal Items in QuickBooks Desktop Pro in the Item List.

Understanding GROUPING function

As a second step, we are required to work on this result set temporarily to aggregate sales amount and adding the
extra subtotals rows. CTE (Common Table Expressions) can the best choice because it allows us to define temporarily
named result sets. In the result set of the query, the rows that are marked with numbers 1 and 2 indicate the subtotals by the years. The row marked with the number 3 shows the grand total of the whole sales of the years.

You might then wonder why we use “subtotal” at all since we will ultimately include it in the overall total anyway — and that’s a valid question that we hope to address in the sections to follow. Rather than counting the total number of people (which might be a difficult task in a large business), you can instead find out the number of people in each group. That way, you can add all the subtotals up at the end to come up with the total of employees. Let’s quickly look at some of the most common areas you’ll see “subtotal” used today.

• While a subtotal is the sum of a category or subgroup, a total is the complete calculation of all sets of categories or subgroups (source).
• In this result set, we need to remove the RowNumber column and only show the SalesMonth column and its’ subtotal
  rows.
• They can appear in everything from personal budgets to household budgets, all the way up to business and government budgets.
• We can see an extra row at the end of the result set, this row shows the grand total sales of the years without
  considering the sales month and quarters.
• A “Subtotal” item totals all the previous items within an invoice or sales receipt.

It would be advisable to fix it, if the sum is the absolute total, the absolute end sum. Anything before the absolute end sum, such as before the taxes are calculated, or the discounts are calculated, is the subtotal. GROUPING SETS is another GROUP BY extension and it allows us to display multiple grouping set in one query. Such as,
when we want to display monthly and quarterly sales in one result set we can use the UNION ALL statement but this
would be an impractical method.

Is It “Subtotal,” “Sub-Total,” Or “Sub Total”?

ROLLUP, CUBE, and GROUPING SETS are extensions of the GROUP BY statement and add the extra subtotal and grand total rows to the resultset. In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set.

Therefore,
we can use this function to identify the NULL values and replace them. If I understand it, in this example, subtotal means the total of all other categories.

In the next section of this article, we will discover these extensions which help to calculate a subtotal in SQL query. Gross income would be what we’d assume as our total when working out how much money we should receive each month. Its calculation would cover things like bonuses, overtime, and commission while also covering allocations like unpaid leave and contributions towards retirement funds.

As seen in the result set, we add a subtotal in SQL query with help of the ROLLUP extension. If, for example, we wanted to calculate the number of employees at a large company, physically counting each one would be an arduous task. Instead, one could speak to managers and build up subtotals of each department, floor, or office block before calculating the final total. In cases like this, our income subtotals would involve adding up all sources of incoming funds — such as salaries, wages, interest, and the likes. They can appear in everything from personal budgets to household budgets, all the way up to business and government budgets.

That means that “subtotal” is less important than “total,” but the “total” needs a “subtotal” before it can be calculated. As you can see, we apply subtotal to things before we end up with the final total. The final total is the sum of all parts added together, which means both adding and subtracting values that are necessary. In the case of a subtotal, we have yet to subtract or add any of the important parts.