Is this query possible?

rcarr

Not sure this is even possible in one query, but I thought I'd run it by the experts. I appreciate any help.

I have the following three tables:

Table: CurrentForecast
Fields: UserID
Fields: Sentiment
Fields: CurrentDow
Fields: CurrentNaz
Fields: CurrentSP

Table: LastUpdated
Fields: lastupdated
Fields: CurrentDow
Fields: CurrentNaz
Fields: CurrentSP

Table: ForecastHistory
Fields: Sentiment
Fields: UserID
Fields: CurrentDow
Fields: CurrentNaz
Fields: CurrentSP

I want my result to group by CurrentForecast.Sentiment.
The LastUpdated table only contains 1 record. I want to use that record in a calculation with the same fields in the CurrentForecast table. All rows in the CurrentForecast table would calculate against the same row in LastUpdated.
The calculation in step 2 will be different based on the value of the field CurrentForecast.Sentiment. There can be 3 possible calculations.
I need to SUM the values in the ForecastHistory using the sentiment field to join with CurrentForecast.

I am not sure if this is enough info, but I appreciate any help.

Thanks.

tomhath

I don't understand what you are trying to do.

1. I want my result to group by CurrentForecast.Sentiment.

So you get one row back for each Sentiment across all UserID's? Or will you only do this for one UserID at a time?

2. The LastUpdated table only contains 1 record. I want to use that record in a calculation with the same fields in the CurrentForecast table. All rows in the CurrentForecast table would calculate against the same row in LastUpdated.

Presumably this happens before the grouping mentioned in #1, but what will be aggregated in the group?

4. I need to SUM the values in the ForecastHistory using the sentiment field to join with CurrentForecast.

You're going to SUM the CurrentDow column? What will that give you?

rcarr

Thanks for the reply. I know it sounds confusing. Sorry.

So you get one row back for each Sentiment across all UserID's? Or will you only do this for one UserID at a time?[/B]

There are three possible options for sentiment. I want a summary, so I need only three records. Here's an example:

The 3 possible options are:

Bullish
Bearish
Neutral

For each, I need to know the number of people that have that sentiment. For example, 10 people Bullish, 22 Bearish, 4 Neutral. The total number of people (i.e. 10+22+4 = 36) would also be the number of records found in the CurrentForecast table. This makes sence since you can only be one of the three options, but not more than one at a time. So I would need a count of the number of people that are Bullish, Bearish and Neutral.

For example:

Sentiment---Number
Bullish 10
Bearish 22
Neutral 4

Taking the query a bit further, for each person I need a SUM of their points from the forecasthistory table, linking by userid.

For example:

Sentiment---Number---History
Bullish 10--175.12
Bearish 22--225.98
Neutral 4--125.28

So at this point I have three records, Bullish, Bearish and Neutral. For each record I have counted the number of people that are of that sentiment, and I have totaled their points from the ForecastHistory table.

The last piece of data I need is a total of their current forecast. The CurrentForecast table contains their current sentiment (i.e.Bullish, Bearish, Neutral) along with the value of the Dow, S&P and Nasdaq at the point they established that sentiment. Since I know what their sentiment was at the time I recorded the values for the Dow, Nasdaq and S&P, I need to determine what the change has been since then. The current value of the Dow, Nasdaq and S&P are stored in the LastUpdated table. This is a one record table, and it simply stores the lastupdate time, along with the current values of the indices.

If you were Bearish and the Dow went down, you earned positive points (CurrentForecast.CurrentDow-LastUpdated.CurrentDow). If you were Bullish, you would have a negative point value. So I would have to reverse the calculation: (LastUpdated.CurrentDow-CurrentForecast.CurrentDow).

So I need to sum this data in my query. I want to add up the points for the current Bulls, Bears and Neutral people. The final result would look something like this.

Sentiment---Number---History---Current
Bullish 10--175.12---987.45
Bearish 22--225.98---456.98
Neutral 4--125.28---345.76

I really appreciate the help. I hope this clarifies the situation a bit more.

Thanks again.

rcarr

I got the query almost working, but I got one problem left.

SELECT count( currentforecast.sentiment ) AS count, currentforecast.sentiment, IF ( currentforecast.sentiment = 'Bearish', SUM( currentforecast.currentdow - lastupdated.currentdow ) , IF ( currentforecast.sentiment = 'Bullish', SUM( lastupdated.currentdow - currentforecast.currentdow ) , 0 ) ) AS DowChange, IF (
currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentnaz - lastupdated.currentnaz ) 6 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( (lastupdated.currentnaz - currentforecast.currentnaz ) 6 ) , 0 ) ) AS NazChange, IF ( currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentsp - lastupdated.currentsp ) 9 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( ( lastupdated.currentsp - currentforecast.currentsp ) 9 ) , 0 ) ) AS SPChange FROM currentforecast, lastupdated GROUP BY currentforecast.sentiment

The only thing that is missing from this query is forecasthistory total. When I attempt to add this part by using a SUM on the forecasthistory.points column with a LEFT OUTER JOIN related to the userid columns in the CurrentForecast and ForecastHistory tables, the calculations get all messed up. The best I can figure is that the correct amounts for the DowChange, NazChange and SPChange are multiplied by the number of records found in the forecasthistory table.

Here's what I am attempting:

SELECT count( currentforecast.sentiment ) AS count, currentforecast.sentiment, IF ( currentforecast.sentiment = 'Bearish', SUM( currentforecast.currentdow - lastupdated.currentdow ) , IF ( currentforecast.sentiment = 'Bullish', SUM( lastupdated.currentdow - currentforecast.currentdow ) , 0 ) ) AS DowChange, IF (
currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentnaz - lastupdated.currentnaz ) 6 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( (lastupdated.currentnaz - currentforecast.currentnaz ) 6 ) , 0 ) ) AS NazChange, IF ( currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentsp - lastupdated.currentsp ) 9 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( ( lastupdated.currentsp - currentforecast.currentsp ) 9 ) , 0 ) ) AS SPChange, SUM(forecasthistory.points) as totpoints FROM currentforecast LEFT OUTER JOIN forecasthistory ON currentforecast.userid = forecasthistory.userid, lastupdated GROUP BY currentforecast.sentiment

Hope someone has the missing peice to this puzzle.

Again, thanks for the help!

rcarr

I got it!