row results

seno

I have a database with identical entries, i know that using 'DISTINCT' in my query will return one of those identical rows.

If for example 'HRS' is a column in mysql, and once the query (using Distinct) is completed, how can i add the values of 'HRS'?

I have already tried 'Sum(HRS) as TOTAL', but that returns the sum of all the values within 'HRS' in mysql database whereas i am after the sum of all the values 'TOTAL' within 'HRS' as returned by query below.

$query=mysql_query("select distinct sum(HRS) as TOTAL, HRS from mytable group by something");

while($res=mysql_fetch_array($query)){
$total=$res['TOTAL'];
$hrs=$res['HRS'];
}

Can $hrs be totalled in PHP after the query returnsthe results, and how, or is it better to do in mysql but how???

Any help would be appreciated.

seno

😕

tomhath

$query=mysql_query("select sum(HRS) as TOTAL, HRS from mytable group by HRS");

seno

wont this return the sum of all the values of HRS on the database, even the identical rows, which is what i am trying to avoid?

tomhath

Post an example of the data in your database and what you are trying to get.

I haven't tried this on a mysql database but it might work:

select sum(distinct HRS) from mytable

or do it in php:

$query=mysql_query("select distinct HRS from mytable"); 

$total=0;
while($res=mysql_fetch_array($query)){ 
  $total += $res['HRS']; 
}

seno

Hmmmmm,

select sum(distinct HRS) from mytable returns a syntax error in mysql.

good idea though.

tomhath

:mad: I'm pretty sure it works on MSSQL. Probably a mysql 3.2x limitation.

This should also work with MSSQL, maybe mysql 4.x but not mysql 3.2x because subselects are not available:

select sum(t.uniqueHRS)
from (select distinct HRS as uniqueHRS from mytable) t

seno

I went about it another way and simply inserted an addition variable in the while loop:

while($res=mysql_fetch_array($query)){
$total=$res['TOTAL'];
$hrs=$res['HRS'];
$sum_hrs=$sum_hrs+$hrs;
}

Now $sum_hrs is the total for HRS. Works like a charm.

Thanks for all your help.

Seno