Getting MIN/MAX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting MIN/MAX

Author

Topic

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 10:24:56

I'm trying to select MIN/MAX values from a table, but getting the below error for each column:

Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.Origin_Country_Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

My statement:

SELECT
Origin_Country_Name
,Origin_City_Name
,Destination_Country_Name
,Destination_City_Name
,TimeStamp
,PDD
,MIN(PESQ_MOS_N)

FROM [DirectReach].[dbo].[VRESULT_ANALOG_PP]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-08-01 : 10:51:51

Where is Group by Part?

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 10:59:50

quote:
Originally posted by sodeep

Where is Group by Part?

Since it's erroring on each column name, I'm supposed to put GROUP BY for each one? Here are all the errors I get currently:

Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.Origin_Country_Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.Origin_City_Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.Destination_Country_Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.Destination_City_Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.TimeStamp' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'DirectReach.dbo.VRESULT_ANALOG_PP.PDD' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

When I put a GROUP BY and the column name, that only knocks off one error. I can't see it making sense to have GROUP BY for each column.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-01 : 11:06:55

you want to get the minimum value for PESQ_MOS_N for ?

For every Origin_Country_Name & Origin_City_Name ?
For every Origin_Country_Name , Origin_City_Name, Destination_Country_Name & Destination_City_Name ?

KH
[spoiler]Time is always against us[/spoiler]

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 11:15:59

quote:
Originally posted by khtan

you want to get the minimum value for PESQ_MOS_N for ?

For every Origin_Country_Name & Origin_City_Name ?
For every Origin_Country_Name , Origin_City_Name, Destination_Country_Name & Destination_City_Name ?

KH
[spoiler]Time is always against us[/spoiler]

Trying to get MIN and MAX PESQ_MOS_N values for each Origin_City_Name going to each Destination_City_Name.

Basically PESQ_MOS_N is a value for the results of a test between the two cities. I want to get the min and max results across the board from city to city.

Currently, I have a select that will give me all data for city to city:

SELECT
[Origin_Country_Name]
,[Origin_City_Name]
,[Destination_Country_Name]
,[Destination_City_Name]
,[TimeStamp]
,[PDD]
,[PESQ_MOS_N]

FROM [DirectReach].[dbo].[VRESULT_ANALOG_PP]
WHERE RET_CODE = 200
AND TimeStamp >= DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()) - 1, 0)
AND TimeStamp < DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()) + 1, 0)
AND datediff(day,getdate(),TimeStamp)=0 and TimeStamp <= getdate()
AND (((Origin_City_Name = 'Hong Kong')
AND Destination_City_Name = 'Argentina'
OR Destination_City_Name = 'Piscataway NJ'
OR Destination_City_Name = 'Newark, NJ'
OR Destination_City_Name = 'London')
OR ((Origin_City_Name = 'Argentina')
AND Destination_City_Name = 'Hong Kong'
OR Destination_City_Name = 'Piscataway NJ'
OR Destination_City_Name = 'Newark, NJ'
OR Destination_City_Name = 'London')
OR ((Origin_City_Name = 'Piscataway NJ')
AND Destination_City_Name = 'Argentina'
OR Destination_City_Name = 'Hong Kong'
OR Destination_City_Name = 'Newark, NJ'
OR Destination_City_Name = 'London')
OR ((Origin_City_Name = 'Newark, NJ')
AND Destination_City_Name = 'Argentina'
OR Destination_City_Name = 'Piscataway NJ'
OR Destination_City_Name = 'Hong Kong'
OR Destination_City_Name = 'London')
OR ((Origin_City_Name = 'London'))
AND Destination_City_Name = 'Argentina'
OR Destination_City_Name = 'Piscataway NJ'
OR Destination_City_Name = 'Newark, NJ'
OR Destination_City_Name = 'Hong Kong')
ORDER BY Origin_City_Name, Destination_City_Name, PESQ_MOS_N ASC

Just need this final piece :o/

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 13:56:51

Simplified the code:

SELECT
[Origin_Country_Name]
,[Origin_City_Name]
,[Destination_Country_Name]
,[Destination_City_Name]
,[TimeStamp]
,[PESQ_MOS_N]
FROM [DirectReach].[dbo].[VRESULT_ANALOG_PP]
WHERE RET_CODE = 200
AND TimeStamp >= DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()) - 1, 0)
AND TimeStamp < DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()) + 1, 0)
AND datediff(day,getdate(),TimeStamp)=0 and TimeStamp <= getdate()
AND Origin_City_Name = @OrigCity
ORDER BY Origin_City_Name, Destination_City_Name, PESQ_MOS_N ASC

But still can't figure out MIN/MAX. Maybe I could do something with first/last as I step through a loop?

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 16:39:54

Getting warmer....

Atleast this one doesn't give me errors:

DECLARE @OrigCity nvarchar(30)
SET @OrigCity = 'London'

SELECT
[Origin_Country_Name]
,[Origin_City_Name]
,[Destination_Country_Name]
,[Destination_City_Name]
,[TimeStamp]
,MIN(PESQ_MOS_N)
FROM [DirectReach].[dbo].[VRESULT_ANALOG_PP]
WHERE RET_CODE = 200
AND TimeStamp < DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()) + 1, 0)
AND TimeStamp >= DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()) - 1, 0)
AND datediff(day,getdate(),TimeStamp)=0 and TimeStamp <= getdate()
GROUP BY Origin_Country_Name, Origin_City_Name, Destination_Country_Name, Destination_City_Name, TimeStamp
HAVING MIN(PESQ_MOS_N) > '0'
AND Origin_City_Name = @OrigCity

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 17:44:40

For some reason when I run the above code, I get 50+ records back. I should only get 1 for each city.

Anyone out there? Please :o/

mnielsen
Starting Member

17 Posts

Posted - 2009-08-01 : 17:53:54

And I still don't understand why I'm required to GROUP BY every field!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources