Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Application Development » Adding SUM() data to screen and report (Problems generating SQL and populating nondb field on list2 screen)
Adding SUM() data to screen and report [message #2707] Thu, 28 April 2011 05:56 Go to next message
VitalSpark is currently offline  VitalSpark
Messages: 5
Registered: April 2011
Junior Member
I am using a List2 screen to display vet activity for a dog. I would like to display each activity and also a total for the cost of vet bills. This field, total_bill, is nondb.

I have tried to use the inner_sql_select with ' *, sum(vet_bill) as total_bill ' in my activity(list2).php file but, as this approach requires me to complete the 'sql_group_by' field, I lose the ability to display the individual activities on my list2 display. I need to include the * in the SQL statement since I want to display the other fields as well as total_bill.

I did wonder about using _cm_ListView_total() but am unsure how to produce the calculated field. I defined a new nondb field in $fieldarray and tried to populate this using
$rows = this->getData( - with my SQL in here - ) ;
in my activity.class.inc file then assigning
$fieldarray['total_bill'] = $rows['total_bill'];.

The $fieldarray field is created and can be used if populated properly but the SQL that I have written to get the system to populate this is always rejected. The error log shows:

"Cannot extract token from: 'SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id = '1';' (#256).
Error in line 5402 of file '/var/www/includes/include.library.inc'.

I have tried to format my SQL in every conceivable way to see if anything works, as follows:
'SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id = '.$fieldarray['dog_id'].';'

"SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id ='" '.$fieldarray['dog_id']."';"'

'SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id = \''.$fieldarray['dog_id'].'\';'

"SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id = \'".$fieldarray['dog_id']."\';"

Although I have echo'd the text to check that a valid-looking string is being produced, I have even tried hardcoding this to be sure:
"SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id ='1';"

"SELECT SUM(vet_bill) as total_bill FROM activity WHERE dog_id =\'1\';"

None of the above approaches works. If I copy the SQL shown in the error log output and put it into mySQL direct, it works, with or without single quotes around the $fieldarray value or with the value 1 or '1' in the hardcoded version.

Finally, once this is working, how can I display the total beneath the child data, the activity section in this case. I know about using
$structure['inner']['fields'][] = array('total_bill' => 'Total Bill');
but am not sure how to place this field correctly below or even above - the other data since at present, if I hard code it, I get one entry per line and if I use
$structure['outer']['fields'][] = array('total_bill' => 'Total Bill')
then it doesn't seem to work at all. If there is an approach using _cm_ListView_total() then where in the code would I place this call?

The above issues will also apply to a pdf report that I will be creating along similar lines.

I have spent ages looking through the RADICORE documentation to find techniques to help me solve this but so far without luck. Thanks for any help.

RADICORE has helped me to get a long way in a short time with building the core of my system. I am very grateful to you for providing such a useful resource.
Re: Adding SUM() data to screen and report [message #2708 is a reply to message #2707] Fri, 29 April 2011 04:09 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I don't know why you are having so many problems with this as I have used aggregates such as 'COUNT(...) AS count' in several places in my framework. If you take a look in the Dictionary subsystem at the 'List Database' task you will see columns on the right which show the table count and the relationship count. In order to get around the 'group by' problem I have replaced the 'COUNT(...) AS count' with inner selects such as:
$sql_select .= ', (SELECT COUNT(database_id) FROM dict_table
                   WHERE ct_table.database_id=dict_database.database_id) as table_count';
$sql_select .= ', (SELECT COUNT(database_id_snr) FROM dict_relationship
                   WHERE dict_relationship.database_id_snr=dict_database.database_id) as rel_count';


If you want an extra row displayed at the end of the list to show overall totals then you need to check out MySQL's WITH ROLLUP option. This is mentioned in FAQ127.


Re: Adding SUM() data to screen and report [message #2709 is a reply to message #2708] Sat, 30 April 2011 04:29 Go to previous message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I have just updated my FAQ to include How do I include an aggregate in a SELECT statement? which should help you in the future.

Previous Topic: SUM(price)
Next Topic: new field for file upload transaction
Goto Forum:
  


Current Time: Fri Mar 29 09:46:08 EDT 2024

Total time taken to generate the page: 0.01132 seconds