Most frequent instance of string. 1

[HestonJames]

Hello Guys,

I've got the following example dataset for you to play with, basicly its a set of very basic weather data, you'll see at the moment that it calculates the average temperature for the records which are pulled out.

Declare @WeatherReport Table (
	Location_ID int,
	Description varchar(50) Collate Database_Default,
	Temp_C int
)

Insert Into @WeatherReport
Values (1, 'Sunny', 10)

Insert Into @WeatherReport
Values (1, 'Sunny', 15)

Insert Into @WeatherReport
Values (1, 'Sunny', 20)

Insert Into @WeatherReport
Values (1, 'Cloudy', 30)

Insert Into @WeatherReport
Values (1, 'Cloudy', 10)

Insert Into @WeatherReport
Values (1, 'Cloudy', 12)

Insert Into @WeatherReport
Values (1, 'Cloudy', 13)

Insert Into @WeatherReport
Values (1, 'Cloudy', 19)

Insert Into @WeatherReport
Values (1, 'Cloudy', 30)

Select	Avg(Temp_C) As AverageTemperature
From	@WeatherReport

What I'm also looking to do it pull the most common 'description' string, which for the given test dataset would be 'cloudy', this allows me to give a good average description for the weather.

What would you suggest to be the best way at going about that?

Cheers all,

Heston

 

[markros]

select top 1 description, Frequency from (select description, count(*) as Frequency from Weather_Report group by Description) Descr_Freq order by Frequency DESC

 

[HestonJames]

Hey Markos,

Thanks a great deal for the reply! That certainly achieves the correct data as far as the description is concerned However, how can I still have my Avg(Temp_C) selected from the data?

Thanks mate,

Heston

 

[markros]

Just add it as a field, e.g.

select top 1 description, Frequency, (select AVG(TEMP_C) as Avg_Temp from Weather_Report) as Avg_Temp from (select description, count(*) as Frequency from Weather_Report group by Description) Descr_Freq order by Frequency DESC

May be there is a better way to do it, but that's the quick idea that comes to mind and I could not figure another way without trying (and I have to admit I'm too lazy to try).

 

[HestonJames]

Hey Markros,

That works a treat! Efficiency will play a very big part in this query come production as the datasets have the potential to grow very large however at this prototyping stage I just wanted something which will push out the correct results just as proof of concept really.

Maybe someone else will chime in with some ideas.

Many thanks mate, purple star.

Heston

 

[markros]

You're welcome. Instead of the TOP 1 you can use select MAX
but we still need to get a frequency.

I'm wondering would

select MAX(count(*)) as Max_Frequency work?

I guess I need to run the script after all, give me a sec.

 

[markros]

Too bad there is no Edit post feature here. Anyway, you can not use aggregate on another aggregate.

This worked

declare @Avg_Temp int
select @Avg_Temp = Avg(Temp_C) 
From  @WeatherReport

select top 1 [Description], Frequency, [Average_Temp] = @Avg_Temp  from (select [Description], count(*) as Frequency from @WeatherReport
group by [Description]) Freq order by Frequency DESC

 

[HestonJames]

Markros!

That works lovely! Thanks for the input, sorry for the slow reply, with it being a Sunday I wasn't really in a work frame of mind and decided to go and have a beer instead ;-)

Ok, so now this seems to be working nicely I'd like to expand the challenge a little ;-)

As you can see from the example data above we have a Location_ID. Now, I want to pull the same dataset that we've discussed before but for and grouped by each Location_ID in a list. :-D

So if I have a list of location ID, 1, 3, 5, 7 I want 7 rows returned, one for each location giving me the average temperature and description respectively!

How does that take you?

Thanks again mate, you're a super star.

Heston

 

[markros]

with cte_Freq as (select top 1 description, Frequency, LocationID from (select description, LocationID, count(*) as Frequency from Weather_Report group by LocationID, Description) Descr_Freq order by Frequency DESC)

select cte_Freq.*, Avg_Temp from cte_Freq inner join (select avg(Temp_C) as Avg_Temp, LocationID from @WeatherReport group by LocationID) Avg on cte_Freq.LocationID = Avg.Location_ID

Or you can swap - first query be the average - cte and the last = the real query

This is from the top of my head and not tested - I just woke up and need to start preparing going to work.

 

[markros]

Sorry, the above would not work - obviously - and I had to stop my morning routine to write it up.

with cte_Freq as (select description, Frequency, LocationID from, row_number() over (ORDER BY Frequency DESC) Row_Number from (select description, LocationID, count(*) as Frequency from Weather_Report group by LocationID, Description) Descr_Freq)

select cte_Freq.*, Avg_Temp from cte_Freq inner join (select avg(Temp_C) as Avg_Temp, LocationID from @WeatherReport group by LocationID) Avg on cte_Freq.LocationID = Avg.Location_ID And cte_Freq.Row_Number = 1

This is SQL Server 2005 and up solution. It is possible to write a SQL Server 2000 solution, but I need time to find the samples...

 

[HestonJames]

Hey Markros,

Thanks so much for all your input so far, It really is appreciated. That query you've offered up runs but doesn't give me the desired results. Let me offer up a little more test data in the hope that it might aid us a little.

Declare @WeatherReport Table (
    Location_ID int,
    Description varchar(50) Collate Database_Default,
    Temp_C int
)

/* Inserts For First Location */
Insert Into @WeatherReport
Values (1, 'Sunny', 10)
Insert Into @WeatherReport
Values (1, 'Sunny', 15)
Insert Into @WeatherReport
Values (1, 'Sunny', 20)
Insert Into @WeatherReport
Values (1, 'Cloudy', 30)
Insert Into @WeatherReport
Values (1, 'Cloudy', 10)
Insert Into @WeatherReport
Values (1, 'Cloudy', 12)
Insert Into @WeatherReport
Values (1, 'Cloudy', 13)
Insert Into @WeatherReport
Values (1, 'Cloudy', 19)
Insert Into @WeatherReport
Values (1, 'Cloudy', 30)

/* Inserts For Second Location */
Insert Into @WeatherReport
Values (2, 'Sunny', 10)
Insert Into @WeatherReport
Values (2, 'Sunny', 20)
Insert Into @WeatherReport
Values (2, 'Sunny', 25)
Insert Into @WeatherReport
Values (2, 'Sunny', 45)
Insert Into @WeatherReport
Values (2, 'Sunny', 65)
Insert Into @WeatherReport
Values (2, 'Rainy', 23)

Select	Location_ID,
		AVG(TEMP_C) As Avg_Temp
From	@WeatherReport
Group By	Location_ID
;
with cte_Freq as (select description, Frequency, Location_ID, row_number() over (ORDER BY Frequency DESC) Row_Number from (select description, Location_ID, count(*) as Frequency from @WeatherReport group by Location_ID, Description) Descr_Freq)
select cte_Freq.*, Avg_Temp from cte_Freq inner join (select avg(Temp_C) as Avg_Temp, Location_ID from @WeatherReport group by Location_ID) Avg on cte_Freq.Location_ID = Avg.Location_ID And cte_Freq.Row_Number = 1

You'll see we now have 2 locations, and the query at the bottom returned a row for each containing the average temp for that location. We obviously need to factor in the most common description string for the location.

You'll also see a copy of the query you just gave me, notice how it only returns a single row?

Thanks mate, appreciate it.

Heston

 

[markros]

I know why, you need to add PARTITION by LocationID. I was rushing and I need to really run now.

 

[HestonJames]

In the famous words of Paris Hilton.... That's hot! ;-)

Thank you Markros, that seems to work poifectly!!

Heston

 

[markros]

) Glad to help and I did make the train