Query Help: Select Max of Grouped Sum Data

[nico3334]

I have a database that has a Date column (1/1/2007) and a Data column that has numerical data. I am currently running a query to sum the Data column for a certain month and grouped by day (There is more than items of Data for a specific Day). Here is my current query:

SELECT SUM(Data) as Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)

This displays the summed data which is grouped by the Days of the month. This works fine, but I want to display the MAX out of these results. So, the MAX data for a Day of the specific Month. This is what I tried but am getting errors:

SELECT MAX(SUM(Data)) as Max_Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)

I'm not sure what I am doing wrong. Any ideas? Thanks!

[davash6]

I have a database that has a Date column (1/1/2007) and a Data column that has numerical data. I am currently running a query to sum the Data column for a certain month and grouped by day (There is more than items of Data for a specific Day). Here is my current query:

SELECT SUM(Data) as Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)

This displays the summed data which is grouped by the Days of the month. This works fine, but I want to display the MAX out of these results. So, the MAX data for a Day of the specific Month. This is what I tried but am getting errors:

SELECT MAX(SUM(Data)) as Max_Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)

I'm not sure what I am doing wrong. Any ideas? Thanks!

SELECT SUM(Data) as Max_Total INTO TEMP_TABLE
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)
GO
SELECT MAX(Max_Total) FROM TEMP_TABLE
GO
DROP TABLE TEMP_TABLE
GO

[nico3334]

SELECT SUM(Data) as Max_Total INTO TEMP_TABLE
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)
GO
SELECT MAX(Max_Total) FROM TEMP_TABLE
GO
DROP TABLE TEMP_TABLE
GO

Thanks for your reply. But, is there a way to do it without temp tables?

[Motoma]

I have a database that has a Date column (1/1/2007) and a Data column that has numerical data. I am currently running a query to sum the Data column for a certain month and grouped by day (There is more than items of Data for a specific Day). Here is my current query:

SELECT SUM(Data) as Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)

This displays the summed data which is grouped by the Days of the month. This works fine, but I want to display the MAX out of these results. So, the MAX data for a Day of the specific Month. This is what I tried but am getting errors:

SELECT MAX(SUM(Data)) as Max_Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)

I'm not sure what I am doing wrong. Any ideas? Thanks!

You will need to use a subquery:

SELECT
    MAX(intermediate.Total)
FROM 
    (
        SELECT SUM(Data) as Total
        FROM database
        WHERE Month(Date) = 10 
        And Year(Date) = 2007
        Group by Day(Date)
    ) intermediate
 
 
 

[nico3334]

You will need to use a subquery:

SELECT
    MAX(intermediate.Total)
FROM 
    (
        SELECT SUM(Data) as Total
        FROM database
        WHERE Month(Date) = 10 
        And Year(Date) = 2007
        Group by Day(Date)
    ) intermediate
 
 
 

Thank you that works great!

One more thing, would it be possible to also select the "day" of the date that is associated with that Max value?

I tried something like this, but it didn't work:

SELECT
MAX(intermediat e.Total), Max_Day
FROM
(
SELECT SUM(Data) as Total
Day(Date) As Max_Day
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)
) intermediate

[Motoma]

Try:

SELECT
    MAX(intermediate.Total), Max_Day
FROM 
    (
        SELECT SUM(Data) as Total
        Day(Date) As Max_Day
        FROM database
        WHERE Month(Date) = 10 
        And Year(Date) = 2007
        Group by Day(Date)
        HAVING Total = MAX(Total)
    ) intermediate
 

And please use code tags in the future.

[nico3334]

Try:

SELECT
    MAX(intermediate.Total), Max_Day
FROM 
    (
        SELECT SUM(Data) as Total
        Day(Date) As Max_Day
        FROM database
        WHERE Month(Date) = 10 
        And Year(Date) = 2007
        Group by Day(Date)
        HAVING Total = MAX(Total)
    ) intermediate
 

And please use code tags in the future.

Thank you so much for your help. I'm sorry, I'm still getting an error. In SQL Query Analyzer, I get the following message:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Total'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Total'.

So, it's not recognizing the Total Columns in the 2nd last line:

HAVING Total = MAX(Total)

Thanks again :)

[iburyak]

Try this:

SELECT Top 1 SUM(DATA) AS Total
             Day(Date) AS Max_Day
FROM DATABASE
WHERE Month(Date) = 10 
AND Year(Date) = 2007
GROUP BY Day(Date)
order by 1 desc

Good Luck.

[nico3334]

Try this:

SELECT Top 1 SUM(DATA) AS Total
             Day(Date) AS Max_Day
FROM DATABASE
WHERE Month(Date) = 10 
AND Year(Date) = 2007
GROUP BY Day(Date)
order by 1 desc

Good Luck.

Thank you so much for your help!