Count if type = 42

[mentalist]

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

[gizmola]

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;

[mentalist]

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.

 

 

[gizmola]

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.

[Barand]

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.

[Psycho]

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