Forums

Hello,

Can I implement an aggregate function in CC? For example, a MYSQL statement might look like this:

SELECT state_tag
, MAX(date_moved) AS "Date Moved"
, building
, room
, staff
, comments
FROM its_locations
GROUP BY state_tag;

If the "Fields" box in a CC connection represents the SELECT line in the MYSQL query, can I include an aggregate function as shown above and if so, how would it be written in CC?

Hope this is clear. Thanks for your help.
Chris

I tried putting the following in the "Fields" box:

eastList.state_tag, MAX(eastList.date_moved) AS "Date Moved", eastList.building, eastList.room, eastList.staff, eastList.comments

and got the following error:

Unknown column 'MAX(eastList.date_moved) AS "Date Moved"' in 'field list' SQL=SELECT `eastList`.`state_tag` AS `eastList.state_tag`, `MAX(eastList`.`date_moved) AS "Date Moved"` AS `MAX(eastList.date_moved) AS "Date Moved"`, `eastList`.`building` AS `eastList.building`, `eastList`.`room` AS `eastList.room`, `eastList`.`staff` AS `eastList.staff`, `eastList`.`comments` AS `eastList.comments` FROM `its_locations` AS `eastList` GROUP BY `eastList`.`state_tag` LIMIT 30

Hi Chris,

I've experimented a bit with this using this thread as a guide. It looks as though - as a workaround - it might work with this in the Fields box

<?php
return array(
  'MAX(date_moved)' => 'Date Moved',
  'building',
  'room',
  'staff',
  'comments'
);
?>

Note that this will not work with a Model ID in the MAX() row :-(

Bob

Hello Bob and thanks for your quick reply.

I used your code like this:

 <?php
    return array(
       'eastList.state_tag',
      'MAX(date_moved)' => 'Date Moved',
      'eastList.building',
      'eastList.room',
      'eastList.staff',
      'eastList.comments'
    );
    ?>

However, it does not appear to select the latest date. In the picture below you can see in phpmyadmin there are two entries. Row 2177 was moved 2015-01-01. Row 2179 was moved 2015-10-12 (The date_moved column Type is set to 'date').
[attachment=0]aggreate function.jpg[/attachment]
Yet if you look at the query results, row 2177 was displayed.
[attachment=1]aggreate function2.jpg[/attachment]
I'll keep experimenting but any other help would be appreciated.

Thanks
Chris

Wow, some strange behavior going on here. Here is another example of a multirecord.
[attachment=0]aggreate function3.jpg[/attachment]
[attachment=1]aggreate function4.jpg[/attachment]
This time it found the max date (2015-06-04) but for some reason it added the information from the previous record (location_id 9) when it displayed the information. The room should say "Transferred to Facility Property Officer" and there should be a comment in the comment field.

Very strange indeed.

Hi Chris,

If you turn the CC Debugger on then you should be able to see the query being generated - hopefully that will give some clues about what exactly is happening.

Bob

Thanks Bob, quick question though. Where do I turn on Connectivity debugger. I cannot find any option in the main panel nor in an individual connection.

Thanks.

Hi Chris,

It's on Front List > Settings and scroll right down to Display Debug.

Bob

Hi Bob,

Thanks for the Debug tip. I ran the debug and the statement looked fine. So, I tried some experiments in phpmyadmin using the following statement:

SELECT l.state_tag
, MAX(l.moved) AS "Date Moved"
, l.building
, l.room
, l.staff
, l.comments
FROM its_locations l
GROUP BY l.state_tag;

In phpmyadmin it would return the max date in the move column from record 2, but the rest of the return would come from record 1.

I'm sure MYSQL is doing what it is suppose to do, I guess I just need to learn how to manipulate it. I'll check out a forum for MYSQL. If they can give me a solution I may be back if I don't know how to format it for CC.

Thanks for all your input.
Chris.

Hi Chris,

This StackOverFlow answer suggests using ORDER BY to get the latest record to be the first - I think that would work here?

Bob

Thanks so much Bob. I looked at the link and I'm afraid it wouldn't solve my problem. I did find a solution which I'd like to share from from my StackOverFlow question.

SELECT l.*
FROM its_locations l
WHERE date_moved = (SELECT MAX(l2.date_moved)
                    FROM its_locations l2
                    WHERE l2.state_tag = l.state_tag
                   );

I'm afraid I can't even begin to figure out how to configure this correlated sub-query in CC. I looked at the FAQ for configuring complex Where statements but I can't figure it out. Can you help with this Bob?

Thanks,
Chris.

Hi Chris,

With CCv4 we were able to hack the code a little and enable Views. I guess it might be possible to do the same in CCV5 . . . But Views don't accept sub-queries - unless you make the sub-query a view according to this page

A different solution here might be to load all of the data with a join and then use PHP to pre-process it in CC to pick out the latest records in each case. That's probably the simpler solution.

Bob