how to use distinct and sum together?

[Alicia]

Hi,

 

I tried to run this query but it doesn't seem to work. Can some guru assist me on this? thanks

 

$get_rating = mysql_query("select distinct sum(crating), sum(srating) FROM myscore where roundID ='12'") or die(mysql_error()); 

 

there are multiple records with roundID value 12 and some of the records will have the same crating and srating. What I want to accomplish is  do not sum those records with the same crating and srating values.

 

e.g :

srating - crating - roundID

12 - 500 - 12

31 - 500 - 12

12 - 566 - 12

45 - 500 - 12

 

Final output I want is

total srating will be 12 + 31 + 45 ( the record with 12 will be excluded.)

same for total crating where 500 will be calculated once only 500+566

 

Please advise

[S3cr3t]

select sum(DISTINCT(crating)), sum(DISTINCT(srating)) from scores where roundID = 12

[Alicia]

thanks.

 

I tried to use mysql_fetch _array to print out the output like below but why nothing is printed? Is there something wrong with my script? When I run the query in phpmyadmin, it does show the right output but when I tried to echo in php file, it failed to show the output.

 

I used this to print the output : echo $get_rating2['sum(DISTINCT(crating)']

 

Please advise and thanks

[S3cr3t]

select sum(DISTINCT(crating)) as crating, sum(DISTINCT(srating)) as srating from scores where roundID = 12

 

echo $dbResult['crating'];

echo $dbResult['srating'];

[Alicia]

thanks. I did try that but still nothing printed out. Any idea what have I done wrong?

 

$get_rating = mysql_query("select sum(DISTINCT(crating)), sum(DISTINCT(srating)) from myscore where roundID ='12'") or die(mysql_error());

$get_rating2 = mysql_fetch_array($get_rating);

echo $get_rating2['crating']."<br>";

[S3cr3t]

Yeah

 

$get_rating = mysql_query("select sum(DISTINCT(crating)) as crating, sum(DISTINCT(srating)) as srating from myscore where roundID ='12'") or die(mysql_error());

  $get_rating2 = mysql_fetch_array($get_rating);

  echo $get_rating2['crating']."<br>";

[Alicia]

thanks.. it works now...

[Alicia]

is there anyway I can sum number of records I want instead of all records in the table? I tried the query below but it will  total up all records I have in the table

 

select sum(DISTINCT(crating)) as crating from myscore ORDER BY `mysff`.`diff` ASC LIMIT 0 ,  2

 

I want to total the 2 records with lowest value in mysff column only.. please advise.