I was trying to run SQL queries on an access database but our normal notation does not work.
An error occurs when I tried the following query:
SELECT VState, VCity, Count(VCity) AS Count
FROM Event
WHERE Status = 'Complete' OR 'Survey'
GROUP BY VCity
Access gives me an error:
"You tried to exeucte a query that does not include the specified expresion 'VState' as part of an aggregate function"
When I finally found a way to fix that, Access didn't carry out the WHERE clause correctly unless I clearly repeated Status = 'Survey'. So the query ran successfully after the following query was written.
SELECT VState, VCity, Count(VCity) AS Count
FROM Event
WHERE Status = 'Complete' OR Status = 'Survey'
GROUP BY VCity, VState
Why does VState have to be grouped? What if I want to just see VState but not group it in anyway? Is there any way that VState could be "part of an aggregate function" other than putting it before a GROUP BY clause?
Also is it specific only in Access where the OR function requres that you specify the attribute again? Or is there something wrong with my notation so that Access doesn't recognize it.
Comments