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.

  • How do I remotely debug a stored procedure within the same domain?
  • Extract numbers from a text in SQL Server
  • Sql Server Ce 3.5 Identity insert
  • After installing mssql driver, I get driver not found exception in php (MAC OS 10.11.3)
  • Why my ASP.NET / SQL Server web app crashes when resumed after a period of inactivity?
  • Converting user-entered search query to where clause for use in SQL Server full-text search
  • 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
        -- subtotals
            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)
        -- grand total
            '' 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 char/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).

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.