Need to know how to group by to get correct count 1

[elsenorjose]

In the following sample data, I have a field called x_duration which is the duration in seconds for a particular record in the database. The table is denormalized so that one row equates to one 'play' or 'session' for a particular track.

I need to create a view to support a reporting application where, among other metrics, I need to show how many records we have where x_duration >=15 seconds and how many records we have where x_duration >=20 seconds. Additionally, I need to sum the x_duration for all records.

station_id cprog artist track x_duration
KDMX-FM VOD A_Perfect_Circle Imagine 21
WTKX-FM VOD Underoath Its_Dangerous_Business_Walking_Out_Your_Front_Door 19
WTKX-FM VOD Saosin Bury_Your_Head 16
KAFX-FM VOD AB_Quintanilla_III Sshh!! 25
KNCN-FM VOD Kumbia_Kings Reggae_Kumbia 20
KAFX-FM VOD AB_Quintanilla_III Pachuco 20
WTKX-FM VOD Saosin Voices 28
KSAS-FM VOD MIMS This_Is_Why_Im_Hot 16
CCM-IP VOD Coldplay Yellow 26
WKQQ-FM VOD 50_Cent Hustlers_Ambition 22
WFKS-FM VOD 50_Cent Best_Friend 19
WJRR-FM VOD AFI Miss_Murder 27
WLTW-FM VOD AFI Miss_Murder 18
WZBQ-FM VOD AFI Miss_Murder 21
WLWD-FM VOD ATeens Sugar_Rush 24
WXKS-FM VOD ATeens Sugar_Rush 16
KHMX-FM VOD ATeens Sugar_Rush 22
WTFX-FM VOD Living_End,_The Prisoner_of_Society 27
WZBQ-FM VOD Akon I_Wanna_Love_You 27
KYLD-FM VOD Akon Lonely 24
KYLD-FM VOD Akon Lonely 15
WZBQ-FM VOD Akon Smack_That 30
KIIS-FM VOD Aly__AJ Rush 22
WJRR-FM VOD Angels_and_Airwaves The_Adventure 16
WXKS-FM VOD Avril_Lavigne Girl_Friend 26
KGOT-FM VOD Avril_Lavigne Girl_Friend 27
KHKS-FM VOD Baby_Bash Suga_Suga 28
XHEPR-FM NEWSONDEMAND - - 16


In my example, I have 28 records that are >=15 seconds and 19 records that are >=20 seconds and a total x_duration of 618 seconds.

I'm not quite sure on my grouping for this query so that I can count both the records that meet my criteria but also still be able to sum the total x_duration across all records in the DB.

Any help is GREATLY appreciated.

 

[elsenorjose]

My apologies...I really should have expanded on this.

In addition to my metrics listed above, I need to count the number of distinct station_id's, I need to count the number of tracks (not distinct), and calculate the average duration of each track.

I realize this would impact the final query so my apologies for not including it in my first post.

Also, this is on MySQL 5.0.26 on Windows XP.

Thanks.

 

[elsenorjose]

Never mind...figured it out

Can I give myself a star?

 

[r937]

i don't think you can

however, if you post the solution ...

r937.com | rudy.ca

 

[elsenorjose]

It wasn't so much the grouping as it was the right logic...

...sum(if(x_duration>=15,1,0)) as 'min15',
sum(if(x_duration >=20,1,0)) as 'min20,
round(sum(x_duration)/60/60,2) as 'ATH''...

So in my reporting app or query, I just sum(min15) or sum(min20) and can still sum(ath) to get my total duration.

 

[r937]

i would use CASE instead of IF

otherwise, nice job



r937.com | rudy.ca