SubTotals and Grand Totals

Talk about programming tricks and examples

Moderator: alexandrleonenko

daviddelmonte
Master
Master
Posts: 159
Joined: Feb 23rd, '10, 15:06

SubTotals and Grand Totals

Postby daviddelmonte » Jul 13th, '10, 17:47

The summarize function on a field seems to provide a page total.

Is there a way to create a Grand Total across all records - do I need to script that in JS?

Thanks

administrator
Site Admin
Site Admin
Posts: 6011
Joined: Jan 7th, '09, 23:18
Contact:

Re: SubTotals and Grand Totals

Postby administrator » Jul 14th, '10, 09:18

You may do it by yourself, using SUM() in SELECT SSL and then showing it in appropriate column

daviddelmonte
Master
Master
Posts: 159
Joined: Feb 23rd, '10, 15:06

Re: SubTotals and Grand Totals

Postby daviddelmonte » Jul 14th, '10, 10:14

I am still not fluent with Obj-PHP..

Can I use a second SELECT statement? How/where do I fold a SUM into this statement:

Code: Select all

$sql="SELECT prints.print_key,
negatives.print_title,
   contacts.first_name,
   contacts.last_name,
   prints.sale_price,
   prints.currency,
   negatives.image,
   editions.negative_id,
   prints.edition_id,
   prints.print_id,
   prints.date_printed,
   prints.frame_type,
   prints.frame_width,
   prints.frame_height,
   prints.frame_size_units,
   prints.glass_type,
   negatives.shoot_location,
   negatives.shoot_country,
   CONCAT_WS(' ',contacts.first_name,contacts.last_name) AS fullName,
   CONCAT_WS(' / ',prints.print_id,editions.edition_size) AS printNumber,
   CONCAT_WS(' x ', editions.width, editions.height) AS printSize,
   CONCAT_WS(' x ', prints.frame_width, prints.frame_height) AS frameSize
FROM negatives
INNER JOIN editions ON negatives.negative_id = editions.negative_id
    INNER JOIN prints ON editions.edition_id = prints.edition_id
    LEFT OUTER JOIN contacts ON prints.contact_id = contacts.contact_id";


I want to SUM prints.sale_price and group by print.currency.

Many thanks

administrator
Site Admin
Site Admin
Posts: 6011
Joined: Jan 7th, '09, 23:18
Contact:

Re: SubTotals and Grand Totals

Postby administrator » Jul 15th, '10, 07:50

yes, you can - just add new column in you SQL, like: SUM(x) as my_sum or (SELECT SUM(x) FROM table) as my_sum

daviddelmonte
Master
Master
Posts: 159
Joined: Feb 23rd, '10, 15:06

Re: SubTotals and Grand Totals

Postby daviddelmonte » Jul 15th, '10, 08:09

Thanks. Do you have any example pages where SUM or COUNT are used?


Return to “Programming Tricks”