AOR Grouping - Currency Issues

English Language - Forum Advanced OpenSales, Workflow, Portal support
1

Hi,

We have a custom field in AOS quotes for non-purchase reason, against which we’d like to run a report totalling quote values per reason, so we can see most frequent reasons for lost business.

I set up a GROUP BY in AOR on the reason_lost_c, expecting to only see one instance of each reason, with the sum of all the grand total (default currency) values as the second column, but where there are quotes in multiple currencies, I’m getting one group per currency and per reason.

The query below was generated by AOR, and introduces the currency ID, which adds another grouping condition which isn’t specified on the front-end (see screenshot below). The query I really need is minus any mention of currency_id in the SELECT or GROUP BY.

SELECT 
    `aos_quotes`.currency_id AS 'aos_quotes_currency_id',
    SUM(`aos_quotes`.total_amount_usdollar) AS 'Total0',
    `aos_quotes_cstm`.reason_lost_c AS 'Reason_Lost1'
FROM
    `aos_quotes`
        LEFT JOIN
    `aos_quotes_cstm` `aos_quotes_cstm` ON `aos_quotes`.id = `aos_quotes_cstm`.id_c
WHERE
    (`aos_quotes_cstm`.reason_lost_c != '')
        AND aos_quotes.deleted = 0
GROUP BY `aos_quotes_cstm`.reason_lost_c , `aos_quotes`.currency_id
ORDER BY SUM(`aos_quotes`.total_amount_usdollar) DESC
LIMIT 0 , 20

Do you have any ideas how can I achieve what I’m aiming to achieve in AOR, or is this a bug in the query generation?

Thanks for any help!
Sam