How to get sums of table column for different groups of data?

Related searches

I have the following table:

Code |  Name     | Store    |  Balance 
------------------------------------- 
56   | product1  |   NY     |   10
56   | product1  |   CH     |   100
56   | product1  |   DE     |   1000
56   | product1  |   AL     |   10000
56   | product1  |   PR     |   10
56   | product1  |   ST     |   10
56   | product1  |   OH     |   10000

57   | product2  |   NY     |   10
57   | product2  |   CH     |   100
57   | product2  |   DE     |   1000
57   | product2  |   AL     |   10000
57   | product2  |   PR     |   10
57   | product2  |   ST     |   10
57   | product2  |   OH     |   10000

58   | product3  |   NY     |   10
58   | product3  |   CH     |   100
58   | product3  |   DE     |   1000
58   | product3  |   AL     |   10000
58   | product3  |   PR     |   10
58   | product3  |   ST     |   10
58   | product3  |   OH     |   10000

What I need to get is:

Code |  Name     | Store       |  Total Balance By Stores
-----------------------------------------------------
56   | product1  |   NY,PR,ST  |   30
56   | product1  |   CH        |   100
56   | product1  |   DE        |   1000
56   | product1  |   AL,OH     |   20000

57   | product2  |   NY,PR,ST  |   30
57   | product2  |   CH        |   100
57   | product2  |   DE        |   1000
57   | product2  |   AL,OH     |   20000

58   | product3  |   NY,PR,ST  |   30
58   | product3  |   CH        |   100
58   | product3  |   DE        |   1000
58   | product3  |   AL,OH     |   20000

By the words - They are 7 stores from which 3 are in one country (NY,PR,ST), 2 are in another country (AL,OH) and 2 are in other individual countries (CH and DE). What I need is to get total balance, i.e. SUM for the group of the NY,PR,ST (for each individual product), the same for AL,OH, and also balance for CH and DE.

I have already tried things such as GROUP BY, UNION, etc. but all far from a satisfiable result

The database server in question is MS SQLSERVER.

The solution can be anything e.g. a stored procedure or SQL query

EDIT: DELETED the wrong store's names from the question (Before it was A1, A2, A3 so all the solutions with the LEFT were fine.) I am very sorry for this mistake...

The solution with LEFT will not work with the corrected names.

I'm very sorry for this confusion - I tried to keep things simple so I've named the stores here as A, B, C, D... I didn't have a clue of the LEFT function and what confusion it will cause... So sorry once more...

This is an answer to your edited question. As I mentioned in a comment, the way you designed your database is certain (not almost) that will cause future problems when there will be changes, like new stores or new stores in new countries. Anyway, a solution to the current state is this:

select
  t.code Code, 
  t.name Name, 
  t.storegroup Store, 
  sum(t.balance) [Total Balance By Stores]
from (
  select *,
    case 
      when store in ('NY', 'PR', 'ST') then 'NY,PR,ST'
      when store in ('AL', 'OH') then 'AL,OH'
      else store
    end storegroup
  from tablename
) t
group by t.code, t.name, t.storegroup

SQL SUM() with GROUP by, To get data of 'agent_code' and the sum of 'advance_amount' for Sample table: orders SQL SUM() using multiple columns with group by. Example: Sum Values By Group Here we have this excel table that contains data of three companies with some random data. First and best method is to use pivot tables. Pivot table is best to get summarized and structured data by groups. But for any reason if you want to sum values by group in the original table only. Then use this method. First sort the table by groups.

You need to group by code, name and left(store, 1) and sum over balance:

select
  code, name, left(store, 1) store, sum(balance) [Total Balance By Stores]
from tablename
group by code, name, left(store, 1)

Excel formula: Sum by group, To subtotal data by group or label, directly in a table, you can use a formula based Note: data must be sorted by the grouping column to get sensible results . When you have a data table in your worksheet you can insert the Total Row option for summing up the data in a table. For inserting the Total Row, first, select any cell of the table and in the Design tab, select the Total Row under the Table Style option. You will see the sum value of column D after selecting the Total Row option. It means the total sales for March will be seen with this.

First, you must construct the column that has the information as you describe - by taking the first letter:

select left(store,1) as Store from yourtable

Now you can include this column in your GROUP BY:

select Code,Name,left(store,1) as Store,sum(Balance ) as [Total Balance By Stores]
from yourtable
group by Code,Name,left(store,1)

How to Group and Summarize your Results (simple explanation , The SQL GROUP BY Clause is used to output a row across specified column values it It is typically used in conjunction with aggregate functions such as SUM or Rather than returning every row in a table, when values are grouped, only the In other words, using our example, with the GROUP BY, we can calculate the� Note: data must be sorted by the grouping column to get sensible results. How this formula works. The framework of this formula is based on IF, which tests each value in column B to see if its the same as the value in the "cell above". When values match, the formula returns nothing (""). When values are different, the IF function calls SUMIF:

CREATE TABLE #sum
    ([Code] int, [Name] varchar(8), [Store] varchar(2), [Balance] int)
;

INSERT INTO #sum
    ([Code], [Name], [Store], [Balance])
VALUES
    (56, 'product1', 'A1', 10),
    (56, 'product1', 'B', 100),
    (56, 'product1', 'C', 1000),
    (56, 'product1', 'D1', 10000),
    (56, 'product1', 'A2', 10),
    (56, 'product1', 'A3', 10),
    (56, 'product1', 'D2', 10000),
    (57, 'product2', 'A1', 10),
    (57, 'product2', 'B', 100),
    (57, 'product2', 'C', 1000),
    (57, 'product2', 'D1', 10000),
    (57, 'product2', 'A2', 10),
    (57, 'product2', 'A3', 10),
    (57, 'product2', 'D2', 10000),
    (58, 'product3', 'A1', 10),
    (58, 'product3', 'B', 100),
    (58, 'product3', 'C', 1000),
    (58, 'product3', 'D1', 10000),
    (58, 'product3', 'A2', 10),
    (58, 'product3', 'A3', 10),
    (58, 'product3', 'D2', 10000)
;
select Code,Name,LEFT(store,1)store ,SUM(balance) from #sum
group by Code,Name,LEFT(store,1)

Excel formula: Sum by group, Here are some more examples of how to summarise data by group using dplyr summarise_all(sum) # summarise all columns except grouping columns using " sum" library(data.table) data = data.table(Category=c("First","First","First"," Second" While I have recently become a convert to dplyr for most of these types of� select t.code Code, t.name Name, t.storegroup Store, sum(t.balance) [Total Balance By Stores] from ( select *, case when store in ('NY', 'PR', 'ST') then 'NY,PR,ST' when store in ('AL', 'OH') then 'AL,OH' else store end storegroup from tablename ) t group by t.code, t.name, t.storegroup

How to sum a variable by group, You can sum a column of numbers in a query by using a type of function called an aggregate function. Aggregate functions perform a calculation on a column of data and return a single value. Access provides a variety of aggregate functions, including Sum, Count, Avg (for computing averages), Min and Max.

Click the PivotTable. On the Analyze tab, in the PivotTable group, click Options. In the PivotTable Options dialog box, on the Totals & Filters tab, do one of the following: To display grand totals, select either Show grand totals for columns or Show grand totals for rows, or both.

To sort the column that contains the data you want to group by, select that column, and then on the Data tab, in the Sort & Filter group, click Sort A to Z or Sort Z to A. On the Data tab, in the Outline group, click Subtotal .

Comments
  • Wow....great. Thank you, thank you, thank you... It is exactly what I wanted! I think there is a lot of things I must learn in SQL, e.g. I didn't know about grouping by the way of "storegroup" in your answer :-) BTW, it is not me who designed the database - the database is "as is" and I must work with it as I can :-) Thank you once more!
  • Thank you for your answer! See my corrected (edited) question, please.
  • Your corrected question has nothing to do with the original question and all the answers you received so far. Since there is no way to distinguish between the stores the way you describe it, you'd better make changes to the design of the database and create a table where you will store countries and the stores that belong to each country. Hardcoding lists like (NY,PR,ST) is indicative of poor design and almost certain to cause problems later when you will need to add or remove stores or countries.
  • Thank you for your answer! See my corrected (edited) question, please.
  • Thank you for your answer! See my corrected (edited) question, please.