By littlemisssunshine on

Hi,

I have a content type for miles where every node has different figures for the miles and I need to calculate the total number of these miles for the entire site and by user who posts the miles.

I've tried looking at Views Calc but the views returns the last node in the view and not the sum. I've tried Custom Views Field but my PHP/MySQL isn't good enough to work out how to get the data out of the table.

I was hoping someone could point me in the right direction for a solution that works? As after I have the totals I need to make calculations.

Edit - Sorry maybe I should have posted this in the Views issue queue but I'm unsure how to move it now?

Thanks.

⋅ Categories: Drupal 6.x

Comments

smitarai’s picture

smitarai commented

$sumforentiresite=db_result(db_query("SELECT sum(t1.fieldwhrmileissave) FROM {table1} t1 JOIN {node} n ON t1.nid=n.nid WHERE n.type='YOURNODETYPE' AND n.status=1");
The above will work if you are saving the field in a custom table, which doesnt have vid(versionid) saved.
For a field from the cck, you will need to specify the nid=vid in t1, if versioning is turned off. If you provide more info, I can help you out.

$sumforuser=db_result(db_query("SELECT sum(t1.fieldwhrmileissave) FROM {table1} t1 JOIN {node} n ON t1.nid=n.nid WHERE n.type='YOURNODETYPE' AND n.status=1 AND n.uid=%d", $uid);
$uid can be either from the global user, in that case add above the query global $user; $uid=$user->uid;

Hope the above query helped.

littlemisssunshine’s picture

littlemisssunshine commented

Thanks for your help, I've actually got it working using: 

$total = db_result(db_query("
SELECT SUM(field_miles_value)
FROM content_type_miles, node
WHERE content_type_miles.nid=node.nid and node.status='1'
"));
Return $total;

I'm not sure if this is the best way though...

drupaal’s picture

drupaal commented

Hi, I'm building a sports website, Cricket, if you like to know which sports. Anyway, I've a content type, "player_stats" and in it I've one of the field, "field_runs_scored". Now I've another field called, "field_select_player" which is a node reference. So when I create the node, I first select the player and then filled up other fields including the "field_runs_scored". Now in views I've created a view for it and using the table view I'm displaying all the fields I want to display. I'm also displaying a chart for couple of field, "field_runs_scored" and "field_wickets_taken", as an attachment. I'm using filters as well to select different players and other criteria as well. No I wanted to do is to display another attachment and show the Total Runs scored for the specific player selected. I've no idea how I can do that. Tried so many things but no luck so far. I also wanted to do other calculations as well like Average score, Highest score, etc... but I guess once this is figured out then rest will be easy. Saw this post so thought may be you can help me. I'll really appreciate your help. Here's the link of that page in case you wanna look at it,
http://afcainc.com/player-stats

Thanks.

smitarai’s picture

smitarai commented

Sorry, I cant help. I am better with queries, bad with views :(
Never took the time the understand views thoroughly, I just get along there.

littlemisssunshine’s picture

littlemisssunshine commented

Have you got access to your phpmyadmin? For all of my calculations, I used views to generate the relationships between the tables and then took the query into phpmyadmin, made it a lot simplier and worked out all the SUMs, COUNTs etc in there for the selected fields.

I then took the query back into Views, used Views Custom Field and printed the results with one calculation per field.

Pun-1’s picture

Pun-1 commented

a way for someone who doesn't really know sql to get this done?