vb.org Archive - SQL Statement help - Sum

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - SQL Statement help - Sum - Resource ID #81 (https://vborg.vbsupport.ru/showthread.php?t=149206)

aggiefan

06-08-2007 02:49 AM

SQL Statement help - Sum - Resource ID #81

I'm coding a page in a new hack I'm building and am trying to sum a column using a where condition. Here's the code I'm using:

Code:

$pledgeamount=$vbulletin->db->query("SELECT SUM(donation_amount) from " . TABLE_PREFIX . "donations where donation_sport='$football'");

$pledge=$pledgeamount['donation_amount'];

Essentially, I want the sum of all values in the donation_amount column where the donation_sport column is football. I just want to display the result -- no other special use for it. I'm getting a "RESOURCE ID #81" when I print $pledgeamount, so thought maybe I had to clean it up and so did the $pledge code part...that just comes up blank.

I've done hacks before for Vbulletin, but never had to use a Sum statement. Is there something special I need to do?

Adrian Schneider

06-08-2007 03:13 AM

Instead of using the query() function, use query_first() which will also fetch the results as an array.

aggiefan

06-08-2007 03:31 AM

Thanks for the reply. I was able to find a work-around (make it work) by looking at the code for vbBookie...i figured they were adding something in there. With that said, here's code that works (maybe not efficient, but a starting point for somebody in the same position I was).

Code:

$pledgetotals=$db->query_read("SELECT SUM(donation_amount) pledges FROM " . TABLE_PREFIX . "donations where donation_sport='$football'");

$totalpledges = $db->fetch_array($pledgetotals);

$finalamount=$totalpledges[pledges];

PRINT "$finalamount" shows the correct sum, so that's about all I needed to be functional here. I may need to expand off this, but it's a start.

Adrian Schneider

06-08-2007 03:33 AM

query_first() basically calls query_read() and fetch_array() to save time. :)

Dismounted

06-08-2007 10:15 AM

And calls a free_result as well. :)

turnipofdoom

06-10-2007 06:25 PM

The reason the first query does not work is because the result field name ends up something like SUM(donation_amount), not donation_amount as you expected. With SUM you generally use AS.

SELECT SUM(my_field) AS SomeName FROM X GROUP BY Y;

All times are GMT. The time now is 08:16 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00941 seconds Memory Usage 1,717KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (6)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: