sciencebear Posted March 2, 2010 Share Posted March 2, 2010 I'm running a query that grabs all the info from several rows of a table called updates. Updates has a column called "type," among other columns. For simplicity, we'll say the types are a, b, c, d, and e. There is also another column, user, which says which user is attached to which update. The query takes each row in order from most recent to least. It works great. However, I want to do something different if there are more than one of a specific type from a certain user in a row. Like this type user a 1 b 2 c 1 c 1 d 1 d 2 e 1 I would like to instead of showing: "1 did a" "2 did b" "1 did c" "1 did c" "1 did d" "2 did d" "1 did e" Show something like this: "1 did a" "2 did b" "1 did c twice" "1 did d" "2 did d" "1 did e" Any suggestions? Sorry if I'm not making a lot of sense. If you have questions, I can try to answer them. Quote Link to comment Share on other sites More sharing options...
aeroswat Posted March 2, 2010 Share Posted March 2, 2010 Look into using the COUNT function of mysql to solve what you are trying to do. Quote Link to comment Share on other sites More sharing options...
jl5501 Posted March 2, 2010 Share Posted March 2, 2010 select user,type,count(type) from updates group by type order by user Quote Link to comment Share on other sites More sharing options...
sciencebear Posted March 2, 2010 Author Share Posted March 2, 2010 Doesn't count count all the elements though? Perhaps I was not being specific enough. I only want to do something different if there are more than one consecutively, so if there was another instance at a different time I don't want to change it. If there's not a plausible way to do that, all the entries have time stamps attached to them. I could settle for doing something different if the time stamps are with a certain amount of time, even if not consecutive, but I don't know how I'd do that either. Quote Link to comment Share on other sites More sharing options...
aeroswat Posted March 2, 2010 Share Posted March 2, 2010 Doesn't count count all the elements though? Perhaps I was not being specific enough. I only want to do something different if there are more than one consecutively, so if there was another instance at a different time I don't want to change it. If there's not a plausible way to do that, all the entries have time stamps attached to them. I could settle for doing something different if the time stamps are with a certain amount of time, even if not consecutive, but I don't know how I'd do that either. Have you tried jl's recommendation? Quote Link to comment Share on other sites More sharing options...
sciencebear Posted March 4, 2010 Author Share Posted March 4, 2010 Have you tried jl's recommendation? I'm trying it right now. Unfortunately, that only results in a row for each different type once, and gives a user along with the total count for that type for every user. I'm not sure how I would implement that to do what I need. Quote Link to comment Share on other sites More sharing options...
sciencebear Posted March 4, 2010 Author Share Posted March 4, 2010 Hmm... I got close with this: SELECT *,COUNT(type) FROM updates GROUP BY user,type,date ORDER BY id DESC This does what I want except the time period is an entire day instead of only 20-30 minutes apart. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.