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 |
|
goligol
Posting Yak Master
128 Posts |
 Posted - 2011-06-07 : 11:05:37
|
| in the following table, some records have exactly same key sets as ODMC with different value at TM0, TMcv, and the TMf is a character(s) which I want to keep.What I want to have is one record with the key set ODMC and the average on TM0, TMcv, and TMv, and keep (s) for TMf where T0 (another column) is not "999999" in one of the duplicated record, otherwise keep the record which its T0 value is not "999999". For example in the first two records I want to keep one with the smae ODMC key and the average of TM0, TMcv, since T0 is not "999999" . but for example in the records 3 and 4 since the T0 is "999999" I do not want to do the averaging but keep the record which its T0 is not "999999" and remove the record with T0 = "999999"Is that make sense?O D M C T0 TM0 TMf TMcv 01000US 001 1 $0000 3344658 s 3.6 14498011329505301000US 001 1 $0000 2894251 s 4.2 14776479133165801000US 001 3 $0000 1342104 z 1.4 3530436567759.3601000US 001 3 $0000 999999 z 1.7 3220582699443.2401000US 001 5 $0000 286457 1.7 237146501133.61Statement true:select O ,D ,M ,C , TMf, TM0=avg(TM0), TMcv = avg(TMcv), where T0 is not '999999' if T0 = '99999' ...(in the duplicated records keep the one where its T0 is not "999999"Thank you |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-07 : 21:35:08
|
| >> in the following table, some records [sic] have exactly same key sets as ODMC with different value at TM0, TMcv, and the TMf is a character(s) which I want to keep. <<Rows are not records and keys are unique by definition. This makes no sense. People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it. Be polite and post the DDL, then we will try to help you.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
 |
|
|
goligol
Posting Yak Master
128 Posts |
Posted - 2011-06-08 : 10:03:36
|
| Thank you. I am very new in SQLServer. I am not familiar with the DDL and standard format of posting questions. however, I have to prepare some tables to use in GAMS. please accept my apology for my poor interpretation. |
|
|
|
|
|
|
|
|
How to write this command in SQL