Custom SQL Query help!

Hello,

I have a query which I have tested in phpmyadmin and pulls in the results I need but I am not sure how to apply this query to CC.

SELECT column1,SUM(column2) FROM `table_name`
GROUP BY column1

Can anyone give me any advice/help?

Thanks
Mark

Hi Mark,

I'm not sure. I still haven't found my way round the new CC.

I think you can handle the GROUP BY in the ORDER BY box but you have to add an ORDER BY entry too

`column1` GROUP BY `column1`

You can try putting the COUNT into the List View Fields box on the Frontend List Settings | General tab. It may or may not work :-(

`column1`,SUM(`column2`)

or possibly

`column1`,SUM(`column2`) AS `c2`

Bob

Hi Bob,

Thanks a lot for the speedy response!

I think I tried this as suggested but get the following error.

Warning: Invalid argument supplied for foreach() in /public_html/components/com_chronoconnectivity/libraries/connection.php on line 300

Which I presume suggests the query is incorrect?

I found the post below where someone is using their own query in what looks like chronoforms instead of CC would this be a better way to go about it?

http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=5&t=67537

Thanks again for your time.

Hi ctrlmedia,

It's probably a problem with the SUM() :-(

You can turn site debug on temporarily to see all the queries and see exactly what is being generated.

A Custom Code action in ChronoForms will also create a list - and the DB Multi Record Loadesr action might also work as it is a bit more flexible than the CC listing setup.

Bob

Hello,

This may not be possible because Connectivity doesn't let you set the fields loaded from the main table, it loads all fields by default, you can do this with any associated table though, because they have fields "field".

Please contact me through the "Contact Us" page and I will try to add this new small feature and send you the patch🙂

Regards,
Max

Thanks for your input Max,

I think I may have worked a way round the issue but if not I will definitely be in touch.

Thanks again for your help!

Hi,

Just a note, that latest version released few days ago got this feature!🙂

Regards,
Max