Question about summing up different fields in different records

[herman404]

Hi everyone, I have some trouble writing a SQL stored procedure that
can do the following:

We have data in one table in numeric form, but we want to sum the data
in this table based on the values of two different alpha fields. To
illustrate, let me write the following example:

Table with these records:
A B 1.1 2.2 Blah1 Blah1
A B 2.3 5.6 Blah2 Blah2
B C 7.8 9.1 Blah3 Blah3
B C 4.5 1.0 Blah4 Blah4
R F 1.1 4.3 Blah5 Blah5
B A 3.1 2.7 Blah6 Blah6

I need to write a query that will return the following result set from
the above table:
A B 3.4 7.8 Blah1 Blah1
B C 12.3 10.1 Blah3 Blah3
R F 1.1 4.3 Blah5 Blah5
B A 3.1 2.7 Blah6 Blah6

If the alphanumberic keys are the same, the sum the numeric columns up
whilst displaying one one of the records, the blah fields don't matter
if one only one is displayed. Can anyone recommend the best way to do
this? I'm running MS SQL 2005.

Thanks,
Herman

[David Portas]

Re: Question about summing up different fields in different records

herman404 wrote:

Hi everyone, I have some trouble writing a SQL stored procedure that
can do the following:
>
We have data in one table in numeric form, but we want to sum the data
in this table based on the values of two different alpha fields. To
illustrate, let me write the following example:
>
Table with these records:
A B 1.1 2.2 Blah1 Blah1
A B 2.3 5.6 Blah2 Blah2
B C 7.8 9.1 Blah3 Blah3
B C 4.5 1.0 Blah4 Blah4
R F 1.1 4.3 Blah5 Blah5
B A 3.1 2.7 Blah6 Blah6
>
I need to write a query that will return the following result set from
the above table:
A B 3.4 7.8 Blah1 Blah1
B C 12.3 10.1 Blah3 Blah3
R F 1.1 4.3 Blah5 Blah5
B A 3.1 2.7 Blah6 Blah6
>
If the alphanumberic keys are the same, the sum the numeric columns up
whilst displaying one one of the records, the blah fields don't matter
if one only one is displayed. Can anyone recommend the best way to do
this? I'm running MS SQL 2005.
>
Thanks,
Herman

SELECT col1, col2,
SUM(col3) AS col3, SUM(col4) AS col4,
MIN(col5) AS col5, MIN(col6) AS col6
FROM your_table
GROUP BY col1, col2 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--