mentalist Posted April 30, 2015 Share Posted April 30, 2015 I have a table with a column named "type", I'm after counting the rows with type 40 - 45 as one count and also counting the rows of type 13 say. Whether this is done using group or what ever I don't mind, but is it possible in a single call? (* There is a where in there too, but) Cheers Quote Link to comment Share on other sites More sharing options...
gizmola Posted April 30, 2015 Share Posted April 30, 2015 Sure you can compute a value and group on the computed column. SELECT IF(type >39 AND type <46, 40, type) types, count(*) FROM foo GROUP BY types; To omit values other than 13 and your range just add a where clause. SELECT IF(type >39 AND type <46, 40, type) types, count(*) FROM foo WHERE type = 13 OR type BETWEEN 40 AND 45 GROUP BY types; Quote Link to comment Share on other sites More sharing options...
mentalist Posted April 30, 2015 Author Share Posted April 30, 2015 Thankyou I only seem to be getting a single group (also the COUNT indicates that too), so i'm going to have my supper and then build a demo to test properly and get to grips with it all. Quote Link to comment Share on other sites More sharing options...
gizmola Posted April 30, 2015 Share Posted April 30, 2015 If you try and alias the name of the computed column back to the same name as the original column, it won't work correctly. Your computed name (and the name you use in the GROUP BY) have to be the computed name. I'm not sure if there's a way to finesse this issue, although Barand might know. With that said, it doesn't explain your result. To get only one group, there seems to be something wrong with your GROUP BY or your WHERE clause. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 30, 2015 Share Posted April 30, 2015 You said that there was already a WHERE clause. Have you added extra conditions to gizmola's WHERE clause using AND? Can you post the actual query you are now running. Quote Link to comment Share on other sites More sharing options...
Psycho Posted April 30, 2015 Share Posted April 30, 2015 (edited) Try this: SELECT if(type=13, '13', '40-46') as type_group, COUNT(*) as type_count FROM table_name WHERE type in (13, 40, 41, 42, 43, 44, 45) Edited April 30, 2015 by Psycho 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.