Page 1 of 1

GROUP BY, totally "total" figures for distinct results

Posted: Thu Dec 18, 2008 6:11 am
by simonmlewis
~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Code: Select all

 
$result = mysql_query ("
SELECT * FROM matterusersrights 
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode 
WHERE matterusersrights.userid = $cookieid");
 
while ($row = mysql_fetch_object($result)) {
    $totalestimate += $row->estimate;
    echo "<br/>
<b> $row->persondealing</b><br/>$row->clientcode-$row->matterno
  $row->estimate<br/>
  $row->total<br/>
  $totalestimate
";
}       
    mysql_free_result($result);
    mysql_close($sqlconn);
Ok - here's what I am trying to do - and failing.
Two tables, one of userid's and rights, and the other showing the data, with the "rights" data in it's own column. The user is given rights to the "clientcode" in matterdata.

I can produce all data for the given clientcode only, and show all the answers, but what I WANT to do is to only show the "persondealing" and the total of all $total and $estimate figures.

So if Joe Bloggs has two entrys. His estimates are £2000 and £500. And his Totals are £1500 and £250.

The results need to be:

Estimate total: £2500
Total total: £1750.

I just can't get it to do it. It produces really strange numbers, that I cannot even equate!

If any of this doesn't make sense, please message me as I am dying to crack it.

Simon


~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: GROUP BY, totally "total" figures for distinct results

Posted: Thu Dec 18, 2008 7:56 am
by jaoudestudios
Can you post your schema and post the results you are getting compared to the results you want?

Re: GROUP BY, totally "total" figures for distinct results

Posted: Thu Dec 18, 2008 8:10 am
by simonmlewis
There is a lot of data.

The table has several fields. The ones in question are "persondealing" (which is someone's name), "total" (which is the total cost so far) and "estimate" (which is how much it SHOULD cost).

I "should" be seeing:

Joe Bloggs (persondealing)
£4,500 (total of all "total" entries for his name, where the users's rights are the same as the client code in that table).
£3000 (As above, but for "estimate").

What I am seeing is odd:
name1
LAF0001-0060 8000
7002
40000
name2
LAF001-0080 4000
2912
44000
name3
LAF001-0096 8000
10048
52000
name1
LAF001-0102 2000
1653
54000
name2
LAF001-0103 10000
1240
64000
The LAF's are 'clientcode'.
Of all the above figures, the top one (ie the last 1240) should be "estimate" and the one below is "total".

Simon

Re: GROUP BY, totally "total" figures for distinct results

Posted: Thu Dec 18, 2008 8:18 am
by jaoudestudios
Try this...it has quote marks only, no other modifications yet :)

Code: Select all

 
$result = mysql_query ("
SELECT * FROM matterusersrights
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode
WHERE matterusersrights.userid = '".$cookieid."'");
 

Re: GROUP BY, totally "total" figures for distinct results

Posted: Thu Dec 18, 2008 8:24 am
by simonmlewis
I now get this - it has split it better.
Need now to see:

No totally of their respective totals/estimate yet.
name1 8000
7002

name2 4000
2912

name3 8000
10048

name1 2000
1653

name2 10000
1240

Re: GROUP BY, totally "total" figures for distinct results

Posted: Thu Dec 18, 2008 8:53 am
by jaoudestudios
Next to those results can you post the results you want?

Also can you post your database schema?

Re: GROUP BY, totally "total" figures for distinct results

Posted: Fri Dec 19, 2008 2:33 am
by simonmlewis
matteruserrights:
id
userid
username
clientcode

matterdata:
loads of fields, but those in question...
id
persondealing
clientcode
matterno
total
estimate

The results I need are as I stated:
persondealing: total of "total" field for all entries for that person, where the clientcode matches the clientcode in matteruserrights.

Ie. Fred is allowed to see clientno LAF001, so all matters that Bill has worked on for LAF001, I want to see a total of those totals. (same for estimate).

Is that more helpful?

S.