Problems geting a count query

[entrari]

First of all, excuse my poor English >_<
I'm having problems with a simple count query. Let's suppose I have this table:
REF -|-FECHA -|-COMUN -|-CLAVE -|-IDENTIFICACION
-------------------------------------------------------------------------
99/122 -|-16/03/1999 -|-LINCE -|-aa1000 -|-M. BOVIS
00/271 -|-19/04/2000 -|-OVEHA -|-aa1000 -|-M. BOVIS
02/0221 -|-20/02/2002 -|-LINCE -|-aa1000 -|-M. BOVIS
99/123 -|-16/03/1999 -|-VACAS -|-aa1258 -|-M. BOVIS
99/124 -|-16/03/1999 -|-LINCE -|-aa1258 -|-M. BOVIS
99/125 -|-16/03/1999 -|-LINCE -|-aa1200 -|-M. CASTA
99/126 -|-17/03/1999 -|-LINCE -|-aa1300 -|-M. CASTA
99/127 -|-18/03/1999 -|-LINCE -|-aa1300 -|-M. CASTA

From a count query i obtain a first query of the number of different “clave”
IDEN -|-CLAVE -|-NUM OF CLAVE
---------------------------------------
M.BOVIS -|-aa1000-|-3
M.BOVIS -|-aa1258-|-2
M.CASTA -|-aa1200-|-1
M.CASTA -|-aa1300-|-2

i need a query that counts the number of different "clave" in the row “Identificacion”, the result should be something like :

IDENTIFICACION -|-NUMBER DIFERENT IDENTIFICACION
--------------------------------------------------
M.BOVIS -|---------------2
M.CASTA -|---------------2
As you see M.BOVIS has two different CLAVE, the aa1000 and the aa1258 ones, the same is for M.CASTA, but the problem is that I cannot find a SELECT query to obtain this table. I’ve tried
SELECT IDENTIFICACION, CLAVE, count(*) as total FROM `main`group by CLAVE,IDENTIFICACION";
But it doesn’t works, and I obtain the middle table. Could anyone help me?

 

[r937]

SELECT IDENTIFICACION
, count(DISTINCT CLAVE) as total
FROM `main`
group by IDENTIFICACION


r937.com | rudy.ca