| Adding SUM() data to screen and report [message #2707] |
Thu, 28 April 2011 05:56  |
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  |
AJM
Messages: 2367
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.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
