Hello Ada Napierala, 

Can you clarify where are you running this query? 

When trying it to run against a MySQL Database the error that I have is related to the opportunities.date_created field that doesn't exist. 
Replacing it with opportunities.date_entered the query runs fine.

Hi Andre Serrazina,

Yes I just realized that I was using incorrect field name. However, I also realized that the field date_closed is showing me the signature date of the opportunity, which is not the date I want to use. 

I want to use the date from activity stream - the date a user changed the opportunity to closed won. 

Is that possible? 

Andre Serrazina

For my advanced report, I am also looking for the following information (apologies, I am a rookie on Advanced Reports in Sugar): 

1. What is the SQL engine of SugarCRM? Is it MySQL, SQL Server, etc? Need to understand this in order to know what syntax I should use for functions. So far I've inferred that it is MySQL, but I just wanted to be sure.

2. Can I perform a subquery inside a query? For example, I would like to do SELECT * FROM (SELECT * FROM opportunities), but it is not letting me. It's outputting a "Page Not Found" error when I try to save the query.

Point 2 is very important for me to compute models for calculations. Thanks!

Andre Serrazina

For my advanced report, I am also looking for the following information (apologies, I am a rookie on Advanced Reports in Sugar): 

1. What is the SQL engine of SugarCRM? Is it MySQL, SQL Server, etc? Need to understand this in order to know what syntax I should use for functions. So far I've inferred that it is MySQL, but I just wanted to be sure.

2. Can I perform a subquery inside a query? For example, I would like to do SELECT * FROM (SELECT * FROM opportunities), but it is not letting me. It's outputting a "Page Not Found" error when I try to save the query.

Point 2 is very important for me to compute models for calculations. Thanks!

SELECT * FROM (SELECT * FROM opportunities) as op

 you should be able to do something like this, it is not working for you 'cos you forgot to add the table name. 

that should work 

Hi, Ada Napierala, on Sugar Cloud Sugar runs MySQL. 

I wonder what the goal of this report is? Maybe we can help more efficient when knowing what you want to archive

Bests

Björn

Thank you Rodrigo Manara

Andre Serrazina thanks for confirming :) 

Hey Björn Canales Pfisterer,

I want to be able to see average opportunity age per month. I am using Advanced Reports at the moment, also to up my SQL skills :) 

In Standard reports, I would have to first create a fields that captures the age of the opportunity, is that correct?

Sounds cool. I will catch up with this including the hint from Andre and check how this can be archived without Advanced reports but using SugarBPM and sugarlogic.

Presumably standard reports might give us a better view on this data :-)

Bests

Björn