Question about summing up different fields in different records

28 Jul 2006 7:54 PM

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

28 Jul 2006 8:00 PM

David Portas

herman404 wrote:
Show quote

> 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
--