[RESOLVED] GROUP BY with a selection

liquorvicar

i am constructing a fairly complicated query on a table that has a field called 'type'. this field has possible values 's','r','c'. what i would like to do is to sum all entries in the table grouped by an id number and then by whether the type is s or not.
i.e. i want results like this:

ID -- type -- sum
1 -- s -- 57
1 -- r+c -- 13
2 --- r+c -- 7
3 -- s -- 5
etc.

can anyone point me in the right direction?

Lars_Berg

select id,type,count(*)
from (select id,case when type = 's' then 's' else 'r+c' end as type
from t) dt
group by id,type

liquorvicar

doesn't work. sorry, should have mentioned that i only have access to MySQL 3.23.58 for this database and hence can't use subqueries!

mrhappiness

SELECT
  id,
  type = 's',
  COUNT(id) the_count
FROM tablename
GROUP BY
  id,
  type = 's'

like that?

for the data you provided in your 1st post this should result in

id | type = 's' | the_count
  1 |         1 | 57
  1 |         0 | 13
  2 |         0 |  7
  3 |         1 |  5

liquorvicar

that's the one, thanks. funnily enough i tried that earlier but i still had type<>'s' in the WHERE clause so of course it didn't work, doh!