SELECT COUNT(A.ParamValue) AS StateCount, S.StateName
FROM tblAppLogger A INNER JOIN
tblStates S ON A.ParamValue = S.StateID AND A.ParamName = 'StateID'
GROUP BY A.ParamValue, S.StateName
ORDER BY StateCount DESC, S.StateName
The above query selects records to display the States and how many times they occur in my table.
I would also like to do the same where ParamName = City, but I want to associate the City with the state searched. Unfortunately because of the way the logging system works, the state is contained in another record, ParamName = 'StateID' & ParamValue = (the actual ID). The commonality is that there is a user Identifier (GuestCookieUUID) and a matching time they submitted their search (DateTimeSubmitted).
Is this even possible?