query 3 table with multiple count !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query 3 table with multiple count !

Author

Topic

christophebmx
Starting Member

1 Post

Posted - 2008-04-05 : 10:27:23

Hi,

I'm a beginner with query sql and i'm french sorry for my english !

i have 3 table in sql server 2005:

PERSONNE: person_id (clef), nom (varchar), prenom (varchar)

FORMATION : formation_id (clef), person (foreign key), titre (varchar), actif (bit)

EXPERIENCE: experience_id(pk), personne (foreign key), description (varchar), actif(bit)

I would like to display the person_ID, the total row for formation actif = 1 by person and formation actif = 0 by person and experience actif = 1 by person and experience actif = 0 by person

the result must be:
person_ID nbFormationActif NbFormationInactif NbExpActfi
1 2 5 4
2 1 2 5
...
NbExpInactif
0
3

I have doing a simple query who display total experience actif and inactif for all people but now i'm must to do the SAME THING in the SAME QUERY FOR FORMATION and i'm still block!!!
someone can help me ?


SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpAct, totExpInactif
FROM 
(
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpInactif
FROM PERSONNE   INNER JOIN EXPERIENCE 
ON PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 0
GROUP BY PERSONNE.Person_ID
)
PERSONNE   INNER JOIN EXPERIENCE 
ON PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 1
GROUP BY PERSONNE.Person_ID, totExpInactif

...

Thanks for all
Christophe

Im' null in query :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 13:46:01

[code]SELECT p.person_id,
COUNT(CASE WHEN f.actif=1 THEN f.person ELSE NULL END) AS nbFormationActif,
COUNT(CASE WHEN f.actif=0 THEN f.person ELSE NULL END) AS NbFormationInactif,
COUNT(CASE WHEN e.actif=1 THEN e.personne ELSE NULL END) AS NbExpActfi,
COUNT(CASE WHEN e.actif=0 THEN e.personne ELSE NULL END) AS NbExpInactif
FROM PERSONNE p
LEFT OUTER JOIN FORMATION f
ON f.person=p.person_id
LEFT OUTER JOIN EXPERIENCE e
ON e.personne=p.person_id
GROUP BY p.person_id[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources