Jump to content

Aggregate function problems


Naive Amoeba

Recommended Posts

Hi,I have this SQL (it's going with ASP and an access database):sql = "SELECT artist, count(artist) AS artist_count, [date] FROM song GROUP BY artist WHERE [date] > " & DateAdd("ww", -1, Date) & " ORDER BY count(artist) DESC"And my DB schema is song(id,artist,song,date,time).I want it to group all rows with a certain artist and count how many occurances there are, but only if the date is logged in the past week. Without the [date] selection and WHERE clause, it works perfectly fine, but selecting [date] along with the other two returns: "You tried to execute a query that does not include the specified expression 'date' as part of an aggregate function."Any ideas?I appreciate any help,Thanks,Matt

Link to comment
Share on other sites

try the query like this

SELECT artist, count(artist) AS artist_count, [date] FROM song GROUP BY artist,[date] WHERE [date] > " & DateAdd("ww", -1, Date) & " ORDER BY count(artist) DESC

When you are getting 'aggregate' errors it is usually because you need to place the problem field in the group by.Most likely you are getting multiple values (duplicate?) that need to be grouped.Let us know if that works.

Link to comment
Share on other sites

try the query like this
SELECT artist, count(artist) AS artist_count, [date] FROM song GROUP BY artist,[date] WHERE [date] > " & DateAdd("ww", -1, Date) & " ORDER BY count(artist) DESC

When you are getting 'aggregate' errors it is usually because you need to place the problem field in the group by.Most likely you are getting multiple values (duplicate?) that need to be grouped.Let us know if that works.

Hey, thanks for the help but no- It doesn't correctly group them now..Compare: http://www.naiveamoeba.co.uk/song/stats.asp to http://www.naiveamoeba.co.uk/song/stats.asp?timespan=dayThe second is with the new SQL stringCheers, Matt
Link to comment
Share on other sites

hmmm...is this an access db? If so could you post a link to it so I can download it and test some queries...it would be much easier than me trying to recreate your db from scratch.Thanks,

Link to comment
Share on other sites

hmmm...is this an access db? If so could you post a link to it so I can download it and test some queries...it would be much easier than me trying to recreate your db from scratch.Thanks,

Hi,I've PMed it to you as I don't particularly want to post my database URI to the public :)Many thanks,Matt
Link to comment
Share on other sites

I played around with the database and could not find a solution. I am pretty sure this can't be done.You are grouping all the plays for each artist together, but there are multiple play dates from each artist...they cannot be displayed in one column.Do you understand what I mean?

Link to comment
Share on other sites

Hi, I dont see if you are using the [DATE] in the SELECT statement anywhere in the code, so if you can write the SELECT without [DATE] field it could be much easier.One more thing should the WHERE come ahead of the GROUP BY (i am not pretty sure abt that in MS Access, but i guess that how it works in sql server) Try this....SELECT artist, count(artist) AS artist_countFROM song WHERE [date] > " & DateAdd("ww", -1, Date) & " GROUP BY artistORDER BY artist_count DESCHTH

Link to comment
Share on other sites

Hi, I dont see if you are using the [DATE] in the SELECT statement anywhere in the code, so if you can write the SELECT without [DATE] field it could be much easier.One more thing should the WHERE come ahead of the GROUP BY (i am not pretty sure abt that in MS Access, but i guess that how it works in sql server) Try this....SELECT artist, count(artist) AS artist_countFROM song WHERE [date] > " & DateAdd("ww", -1, Date) & " GROUP BY artistORDER BY artist_count DESCHTH

Ah well, thanks anyway (to aspnetguy). Oh, and how could you use a where statement when you haven't selected it.. ?ThanksMatt
Link to comment
Share on other sites

Ah well, thanks anyway (to aspnetguy). Oh, and how could you use a where statement when you haven't selected it.. ?ThanksMatt

You do not have to SELECT a column to use it in a WHERE condition.The columns you SELECT are what will be returned when the query is run not the columns you can use in the query.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...