473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

how to group 2 rows into 1 rows?

i have a table 'data' that contain 3 fields-->id1, id2, score
ex:
TABLE 'data'
Expand|Select|Wrap|Line Numbers
  1. -------------
  2. id1|id2|score
  3. --------------
  4.  1 | 3 | 0.5 |
  5.  1 | 4 | 0.6 |
  6.  2 | 3 | 0.4 |
  7.  2 | 4 | 0.8 |
I want to merge the field and update the score --> if id1 is equal to another row. and, it's like this:
Expand|Select|Wrap|Line Numbers
  1. -----------------
  2. id1|id2|id3| score
  3. --------------------
  4.  1 | 3 | 4 | 0.55  -- score is (0.5+0.6)/2 = 0.55
  5.  2 | 3 | 4 | 0.6  -- score is (0.4+0.8)/2 = 0.6
  6. -------------------
how should I do this? I get my query is false---here's my code

Expand|Select|Wrap|Line Numbers
  1. SELECT d1.id1 as d1, d2.id2 as d2, d3.id2 as d3, data.score
  2. FROM data d1, data d2, data d3, data
  3. WHERE d1.data<d2.data
  4.   AND d2.data<d3.data
  5.   AND TRUNCATE((data.score), 1) = 
  6.   (
  7.     SELECT TRUNCATE (AVG ((dt1.score+ dt2.score)/2),2)
  8.     FROM data dt1, data dt2
  9.     WHERE dt1.id1=d1.id1
  10.           AND dt2.id2=d2.id2
  11.           AND dt1.id<dt2.id
  12.   )
Aug 16 '07 #1
1 1617
pbmods
5,821 Expert 4TB
Heya, Fred.

So you are trying to find the average of every score for id1; is this correct? Will there never be more than two scores per id1? What about if there's only one score for id1?

Finding the average score is actually relatively easy:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `id1`,
  3.         AVERAGE(`score`)
  4.             AS `score`
  5.     FROM
  6.         `data`
  7.     GROUP BY
  8.         `id1`
  9.  
The hard part is selecting the IDs that go into determining the average score.
Aug 16 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Robby McGehee | last post by:
I need this to work: SELECT FROM WITH (NOLOCK) where ='a' GROUP BY , HAVING COUNT () > 1 The problem is that I get an error that needs to be in the GROUP BY clause or aggregate function. if...
1
by: Job Lot | last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values: Date Amount Int Balance 1/1/2004 5000.00 50.00 5050.00...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
2
by: JJA | last post by:
Please advise on how to get the GROUP BY coded in an acceptable way: DECLARE @LO INT DECLARE @HI INT DECLARE @StartDate varchar(10) DECLARE @EndDate varchar(10) SELECT @StartDate =...
4
by: David Link | last post by:
Hi, Why does adding SUM and GROUP BY destroy performance? details follow. Thanks, David Link s1.sql: SELECT t.tid, t.title, COALESCE(s0c100r100.units, 0) as w0c100r100units,
5
by: Mike Nolan | last post by:
I notice that 7.4 doesn't do default ordering on a 'group by', so you have to throw in an 'order by' clause to get the output in ascending group order. Is this something that most RDB's have...
3
by: Hyphessobricon | last post by:
Hallo, Indeed, a count of a query with a group by function gives more records than there are and so for-next structures don't function. How is this to be mended. Anyone? Everyone in fact....
12
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id,...
5
by: Roy Gourgi | last post by:
Hi, Is there a way to group rows by some columns in such a way that I can clearly see them in a distinguished fashion when I look at a view or table. This is my code below but when I try using...
2
by: akansha1234 | last post by:
hi friends, I am new to teh community as well as to coding in java. and now i am facing a problem i hope any one can help me out. My problem is that i have some rows i want to group the rows by...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.