MySQL grouping functions

[warden]

Hey guys,

 

I have a query like this:

 

select st.caretype,SUM(IF( ((q.name = "Question1" AND sa.answer = 'Y')

OR (q.name = "Question2")),1,0)) qcnt from staff st

inner join staffanswer sa on sa.staff_id=st.id

inner join question q on q.id = sa.question_id

group by caretype

;

 

This gives me a result set:

+----------+-------------------+

| caretype | count(staff_id)/2 |

+----------+-------------------+

|        3 |            1.0000 |

|        1 |            1.0000 |

|        3 |            1.0000 |

+----------+-------------------+

 

 

The problem is that I want it grouped by care type and I can't do SUM(COUNT(( ...

Any hints?

 

The problem solved in this query is:

Get all the number of *pairs* (so double records from q table for each of staff.id) and group them by caretype from staff table.

[warden]

Ok , I'm almost there with a result

 

+----------+---------------------------+

| caretype | IF(count(staff_id)=2,1,0) |

+----------+---------------------------+

|        3 |                        0 |

|        3 |                        0 |

|        3 |                        1 |

|        3 |                        0 |

|        1 |                        1 |

|        3 |                        1 |

+----------+---------------------------+

 

with query:

 

select caretype,IF(count(staff_id)=2,1,0) from staffanswer sa

inner join question q on sa.question_id=q.id

inner join staff st on st.id = sa.staff_id

where

((q.name = "Question1" AND sa.answer = 'Y')

OR (q.name = "Question2"))

group by staff_id;

 

But i want now to sum the values of IF like

select caretype,SUM(IF(count(staff_id)=2,1,0)) from staffanswer sa

inner join question q on sa.question_id=q.id

inner join staff st on st.id = sa.staff_id

where

((q.name = "Question1" AND sa.answer = 'Y')

OR (q.name = "Question2"))

group by staff_id;

 

but it says invalid use of group function

 

[fenway]

You can't aggregate an aggregate in a single query...

[warden]

great, I know that from this message

but any advice on how to do it the simplest way?

 

thx

[fenway]

Run the COUNT() first, then use this as a dervied table, and SUM that.