Menu
In SQL Server 2012, I have a table my_table that has columns state, month, ID, and sales.
My goal is to merge different rows that have the same state, month, ID into one row while summing the sales column of these selected rows into the merged row.
For example:
state month ID sales
-------------------------------
FL June 0001 12,000
FL June 0001 6,000
FL June 0001 3,000
FL July 0001 6,000
FL July 0001 4,000
TX January 0050 1,000
MI April 0032 5,000
MI April 0032 8,000
CA April 0032 2,000
This what I am supposed to get
state month ID sales
-------------------------------
FL June 0001 21,000
FL July 0001 10,000
TX January 0050 1,000
MI April 0032 13,000
CA April 0032 2,000
I did some research, and I found that the self join is supposed to do something similar to what I am supposed to get.
711
If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
Click Data>Consolidate (in the Data Tools group). In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
Unless I am missing something in the requirements, why not just use an aggregate function with a GROUP BY:
select state, month, id, sum(sales) Total
from yourtable
group by state, month, id
order by id
See SQL Fiddle with Demo
The result is:
| STATE | MONTH | ID | TOTAL |
--------------------------------
| FL | July | 1 | 10000 |
| FL | June | 1 | 21000 |
| CA | April | 32 | 2000 |
| MI | April | 32 | 13000 |
| TX | January | 50 | 1000 |
Considering there should be an index on column id, this query would be a better solution:
select state, month, id, sum(sales) Total
from yourtable
group by id, state, month
order by id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
