Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tegryan
Starting Member
22 Posts |
 Posted - 2002-06-25 : 22:22:07
|
| Hi,I am trying to write a select statement that performs a summation on records in the database with the same value in col_1(usually four or five records). Here is the simplified code:select distinct(col_1) as col_1, col_2, col_3, sum(col_4) as col_4, avg(col_5) as col_5, max(col_6) as col_6, sum(col_7) as col_7, col_8 into Table_Temp from Table1group by col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8order by col_1The goal of course is to create a single record that contains the necessary information from the four or five records. The information in the columns that are not involved in an aggregate function (sum, avg...)is ALWAYS the same.Here is the problem: The query works almost 97% of the time, but occassionally the sum(col_7) part causes the records not to be combined. They are half combined, usually leaving two records. The information is always the same data type in the field (smallmoney) and it appears always to be valid data. Why would this work sometimes and not others? PLEASE, if anyone can shed even a bit of light on this, I would GREATLY appreciate it!!Thanks for reading this far!Edited by - tegryan on 06/25/2002 22:23:18 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-06-25 : 22:27:22
|
tegryan,Why are you grouping on the aggregate columns?Shouldn't the query be..select distinct(col_1) as col_1, col_2, col_3, sum(col_4) as col_4, avg(col_5) as col_5, max(col_6) as col_6, sum(col_7) as col_7, col_8 into Table_Temp from Table1 group by col_1, col_2, col_3, col_8 order by col_1 HTHDavidM"SQL-3 is an abomination.." |
 |
|
|
tegryan
Starting Member
22 Posts |
Posted - 2002-06-25 : 22:32:10
|
| Wow, thanks so much for the quick response!When I changed my SELECT statement from the real column names to col_1 and so on, I did it in a rush. In the real statement I am only grouping by the non-aggregate columns in the select statement, as well as the one in the distinct() function, of course. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-06-25 : 22:51:05
|
| Can you remove the Distinct clause? It is being grouped on already...And how is this proc executed? Stored Proc, View, Dynamic SQL etc..DavidM"SQL-3 is an abomination.." |
|
|
|
tegryan
Starting Member
22 Posts |
Posted - 2002-06-26 : 01:16:35
|
| I need to combine the four or five records down to one. The first col (col_1) is the order number, which is the same accross all five records. I wasn't aware that a group by clause would return a single record if the other information matches. What I mean is, all the fields are identical, except the fields that I'm performing aggregate functions on, which allows me to make one record of the bunch. The statement is being executed in a stored procedure.hope this helps a little!!Note: after I posted this I tried without the distinct in the select statement, and it returns the same results, but it's good to know that it's not needed, thanks!Edited by - tegryan on 06/26/2002 03:49:15 |
|
|
|
tegryan
Starting Member
22 Posts |
Posted - 2002-06-26 : 04:20:30
|
| Whoa, must be the computer fumes. I figured out what the problem was, there was a value that didn't match in the one of the non-aggregate functions, cause the records to stay seperate. Thank you very much for your help anyway. |
|
|
|
|
|
|
|
|
Aggregate select statement misbehaving