Sub query problem

[mama16]

I'm trying to get the highest value. Actually, I did get the highest value, but when I added another field "Month Year" things got messed up( I got all the values and all the dates) so I tried to create a sub query and I don't think I'm doing it right. Can someone please help me with the syntax.

I want to get the highest value along with their dates

Thank you

SELECT Deployers.Deployers, MaxStatistics.Tot_calls_by_Month) As Total
FROM Deployers INNER JOIN Statistics ON Deployers.Deployers = Statistics.Deployers
Where Statistics.MonthYear in

( SelectDeployers.Deployers, Statistics.MonthYear
from Deployers INNER JOIN Statistics ON Deployers.Deployers = Statistics.Deployers);

 

[Golom]

First you need a query to retrieve the Maximum number of calls for each deployer

SELECT 
	S.Deployers, 
	Max(S.Tot_calls_by_Month) As Total
From Statistics As S
GROUP BY S.Deployers

Then you need to integrate that into another query to get the dates associated with those maximum values

SELECT T.Deployers, T.Total, S.MonthYear

From Statistics As S INNER JOIN 

       (SELECT S.Deployers, 
	       Max(S.Tot_calls_by_Month) As Total

        From Statistics As S

        GROUP BY S.Deployers) As T 
        ON S.Deployers=T.Deployers And 
           S.Tot_calls_by_Month = T.Total

You don't appear to need the "Deployers" table because all the information that you want (Deployers, Tot_calls_by_Month AND MonthYear) are in the "Statistics" table.

 

[mama16]

Thank you so much. It did work. Man, you're good.
I did this and I kept getting the highest value of all;
368,125 OR 01-Mar-02


SELECT Statistics.Tot_calls_by_Month, Statistics.Deployers, Statistics.MonthYear
FROM Statistics
WHERE (((Statistics.Tot_calls_by_Month) In (Select Max(Statistics.Tot_calls_by_Month)
from Statistics)));

I did get close though for a beginner.

Appreciate it.