SQL SUM based on first two characters of one column, one table
I’m fairly new to SQL and have the following dilemma. I have the following partial table:
Account Description ShortDescription Balance ------------------------------------------------ 100001 Blah, blah Blah, blah 28350.68 100020 " " 2537.35 111000 " " 86898.12 111001 " " 63943.63 121000 " " 55325.68 121012 " " 65264.35 122000 " " 94898.85
I need to SUM the Balance of all Account starting with the first two digits. So the first two Balance is summed, next two summed, next three summed, etc. Then I need to take those sums and create a grand total.
The end table will include all of the columns, SubTotal, and GrandTotal. I can’t figure out a good way to do this; I tried OVER – PARTITION BY with no success (SELECT SUM(Balance) OVER (PARTITION BY Account) AS SubTotal). Any help would be appreciated. Thanks in advance.
2 Solutions collect form web for “SQL SUM based on first two characters of one column, one table”
This is one way of outputting a table with grand total and subtotals:
-- select individual Rows select Account, Description, ShortDescription, Balance from Accounts union -- subtotals select substring(Account, 1, 2) as Account, substring(Account, 1, 2) + ' Subtotal' as Description, '' as ShortDescription, sum(Balance) as Balance from Accounts group by substring(Account, 1, 2) union -- grand total select '' as Account, 'Grand Total' as Description, '' as ShortDescription, sum(Balance) as Balance from Accounts -- order all records to give the illusion of interlaced subtotals/grand total order by Account desc
However, I think the optimal solution would be to have a plain select from the table and use a reporting tool (such as SQL Server Reporting Services) handle the total/subtotals.
Please not that in the above statement I assumed that the
Account column is of type
varchar. If it is declared as
int or other numeric type, you should add the appropriate cast/convert transformations. (all data sets in a
union must have the same number of columns and the same data types)
I think that the initial problem was that writing partitions correctly is a bit difficult, since the syntax is a bit of a pain. If I undestand what you are looking for (all values and totals in the individual rows,) as a simple alternative to what w0lf provided, here is what may be a quicker solution:
Select *, SUM(Balance) OVER (PARTITION BY substring(Account, 1, 2)) as Subtotal, Sum(Balance) over(Partition by 1) as Total from dbo.Accounts
This gives each of the original columns (*), in addition to a subtotal column, and a total column, using sums over the partitions.
I used the following structure and your data, if you get any errors this might be helpful:
CREATE TABLE dbo.Accounts (Account varchar(10), [Description] varchar(10), ShortDescription varchar(10), Balance money) GO INSERT INTO Accounts (Account, Description, ShortDescription, Balance) VALUES (100001, 'Blah', 'Blah2', 28350.68),(100020, 'Blah', 'Blah2', 2537.35), (111000, 'Blah', 'Blah2', 86898.12),(111001, 'Blah', 'Blah2', 63943.63), (121000, 'Blah', 'Blah2', 55325.68),(121012, 'Blah', 'Blah2', 65264.35), (122000, 'Blah', 'Blah2', 94898.85)
As an addendum, if the Accounts are stored as numbers, you can use substring(cast(Account as varchar(max)), 1, 2)) instead of substring(Account, 1, 2).