Naive Amoeba Posted March 12, 2006 Share Posted March 12, 2006 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 More sharing options...
aspnetguy Posted March 13, 2006 Share Posted March 13, 2006 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 More sharing options...
Naive Amoeba Posted March 13, 2006 Author Share Posted March 13, 2006 try the query like thisSELECT 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. <{POST_SNAPBACK}> 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 More sharing options...
aspnetguy Posted March 13, 2006 Share Posted March 13, 2006 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 More sharing options...
Naive Amoeba Posted March 13, 2006 Author Share Posted March 13, 2006 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,<{POST_SNAPBACK}> 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 More sharing options...
aspnetguy Posted March 13, 2006 Share Posted March 13, 2006 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 More sharing options...
pulpfiction Posted March 13, 2006 Share Posted March 13, 2006 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 More sharing options...
Naive Amoeba Posted March 13, 2006 Author Share Posted March 13, 2006 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<{POST_SNAPBACK}> 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 More sharing options...
pulpfiction Posted March 13, 2006 Share Posted March 13, 2006 Did you try using this query, cos when i tried in sql server it worked fine.. but i am not sure.. jus give it a try..Sorry if i am wrong Link to comment Share on other sites More sharing options...
aspnetguy Posted March 14, 2006 Share Posted March 14, 2006 Ah well, thanks anyway (to aspnetguy). Oh, and how could you use a where statement when you haven't selected it.. ?ThanksMatt<{POST_SNAPBACK}> 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now