Jim Buttafuoco wrote:
Jan, I write queries like this CREATE VIEW parent_childs AS SELECT c.parent, count(c.state) as childtotal, sum(case when c.state = 1 then 1 else 0 end) as childstate1, sum(case when c.state = 2 then 1 else 0 end) as childstate2, sum(case when c.state = 3 then 1 else 0 end) as childstate3 FROM child c GROUP BY parent;
It would help if booleans could be casted to integer 1/0 :-) But performance wise it should be about the same? I think I'll run some tests later today with real data. Would an index on NULLIF(state,1) help count(NULLIF(state,1)) ? Can one build an index on (case when c.state = 3 then 1 else 0 end)? Thanks, Jan
