|
|
Posted by David Portas on 07/28/06 20:00
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
--
Navigation:
[Reply to this message]
|