April 4, 2008 at 5:50 am
Hi EveryBody,
I'm a beginner in t sql and i don't write well english sorry but i have a problem about a create a query with multiple count !
I have 1 table and 1 view:
Table PERSONNE: Person_ID (primary key), Nom, Prenom
View FORMATION: Formation_ID, Person (foregin key personne), Actif (bit), Tit_Obt
On table personne of course, we can find some users and on table formation we can find formation for all personne but the filed actif is set true or false if the formation exist or not !
I want some like this
for all personne, i want to know the COUNT of FORMATION actif = true and false:
sample:
Idpersonne NbActitTrue NbActifFalse
4 4 2
15 1 8
9 0 1
And so on !
But i can't create my select i'm to NULL !
Can you help me !?
Thanks for all
Christophe
April 4, 2008 at 7:04 am
Would something like this do?
select p.person_id as Idpersonne,
sum(case f.actif when 1 then 1 end) as NbActitTrue,
sum(case f.actif when 0 then 0 end) as NbActifFalse
from personne p
join formation f
on p.person_id = f.person
group by p.person_id
/Kenneth
April 4, 2008 at 7:17 am
Hi Kenneth,
i've just copy paste your code it's work BUT there is just one problem who doesn't work !!!
the column NbActifFalse is always set to 0 it's seems that the sum doesn't work for this column!
However the other column is CORRECT with the right sum !
If you have any idea ?
Christophe
April 4, 2008 at 7:28 am
My bad, I'm sorry..
That row should naturally read:
sum(case f.actif when 0 then 1 end) as NbActifFalse
/Kenneth
April 4, 2008 at 7:30 am
sum(case f.actif when 0 then 0 end) as NbActifFalse
This will always be 0 becuase you are summing zero. If you change it to the code below it should work.
sum(case f.actif when 0 then 1 end) as NbActifFalse
April 4, 2008 at 7:33 am
Hi Guys,
I would like to thanks you really because i've search to do this since several hours and i can't !
thanks thanks thanks !
Christophe
April 4, 2008 at 8:02 am
Just one thing i must to understand sorry !
Your query works well and enough easy to understand!
select p.person_id as Idpersonne,
sum(case f.actif when 1 then 1 end) as NbActitTrue,
sum(case f.actif when 0 then 1 end) as NbActifFalse
from personne p
join formation f
on p.person_id = f.person
group by p.person_id
ORDER BY P.Person_ID
Now if i want to add a other table as EXPERIENCE table:
EXPERIENCE: experience_ID (primary key), personne (foreign key), actif (bit)
If i want to know in the SAME QUERY the total of experience of each people actif = 0 and actif = 1 i add to your query this:
select p.person_id as Idpersonne,
sum(case f.actif when 1 then 1 end) as NbActitTrue,
sum(case f.actif when 0 then 1 end) as NbActifFalse,
--add
sum(case e.actif when 1 then 1 end) as NbExpTrue,
sum(case e.actif when 0 then 1 end) as NbExpFalse
from personne p
join formation f
on p.person_id = f.person
--add
join experience e
on p.person_ID = e.personne
group by p.person_id
ORDER BY P.Person_ID
But when i run the query the result is not correct ON EACH COLUMN!
I don't know why, i've just add the same line of code as you and add a join!
If i comment the two line of sum i've just added:
select p.person_id as Idpersonne,
sum(case f.actif when 1 then 1 end) as NbActitTrue,
sum(case f.actif when 0 then 1 end) as NbActifFalse,
--sum(case e.actif when 1 then 1 end) as NbExpTrue,
--sum(case e.actif when 0 then 1 end) as NbExpFalse
from personne p
join formation f
on p.person_id = f.person
join experience e
on p.person_ID = e.personne
group by p.person_id
ORDER BY P.Person_ID
The result it'"s not CORRECT TOO !
As i understand my join is not correct but why !?
The result should be :
Person_ID Nbactif NbInactif NbExpActif NBExpInactif
1 2 1 2 1
5 1 5 6 1
Thanks for all and sorry for my bad english !
Christophe
April 4, 2008 at 8:21 am
Your join is a 1 to many and is skewing your original results. Try using a derived table.
SELECT p.person_id AS Idpersonne,
SUM(CASE f.actif WHEN 1 THEN 1 END) AS NbActitTrue,
SUM(CASE f.actif WHEN 0 THEN 1 END) AS NbActifFalse,
--ADD
MAX(e.NbExpTrue),
MAX(e.NbExpFalse)
FROM personne p
INNER JOIN formation f
ON p.person_id = f.person
--ADD
INNER JOIN(
SELECT Personne,
sum(CASE actif WHEN 1 THEN 1 END) AS NbExpTrue,
sum(CASE actif WHEN 0 THEN 1 END) AS NbExpFalse
FROM experience
GROUP BY Personne
) AS e
ON p.person_ID = e.personne
group by p.person_id
ORDER BY P.Person_ID
Please check the syntax, as I did not parse this
April 4, 2008 at 8:36 am
You are too strong !
That work well very well
But could you tell me if there is a way more easy to do this ?
Because i can read the code but i've enough some difficult to understand the inner join ( select ...) and the max ! If there is a other way more easy you can tell me and i wan to try !?
Thanks for your time
Christophe
April 4, 2008 at 8:53 am
There really is not a better way to achieve this, with simple joins. I can give you the break down on derived tables, as they can be very very beneficial.
First off, the INNER JOIN is doing the same thing a join does with a normal table; however, instead of using a "normal table" we want to use the results of a query, as a table. This is a derived table. In the derived table I aggregated the data for each person, which means only one record is joined to the main query, not 1+ rows, like when you joined experience.
The reason I use MAX is because you are using the group by clause, which means you have to supply an aggregate function because that column is not part of the group by clause. Max normaly grabs the largest value for a column, but we are returning one sum value, so the value will always be equal to the maximum value. This is why I chose MAX().
April 5, 2008 at 9:00 am
Hi both,
Adam => thank for your information about your query but it's perhpas stupid but i can copy and paste your code who works but i m sure not sure to understand it a 100% and if one day i must to explain at someone 🙁 you can imagine !
I have create a solution, don't smile please 🙂 after several hours with my computer and my engine sql and little brain and have built a solution who works.
I have just a question, is it possible to reduce the number of line of code in this query without use the case when of course ?
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpAct
,
( /** QUERY TABLE EXPERIENCE ACTIF = 0**/
SELECT COUNT(EXPERIENCE.Personne) FROM EXPERIENCE
WHERE PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 0
) AS totExpInact
,
( /** QUERY TABLE FORMATION Actif = 0 **/
SELECT COUNT(FORMATION.Person) FROM FORMATION
WHERE PERSONNE.Person_ID = FORMATION.Person
AND FORMATION.Actif = 0
) AS totForInact
,
( /** QUERY TABLE FORMATION actif = 1 **/
SELECT COUNT(FORMATION.Person) FROM FORMATION
WHERE PERSONNE.Person_ID = FORMATION.Person
AND FORMATION.Actif = 1
) AS totForActif
FROM PERSONNE , EXPERIENCE WHERE
PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 1
GROUP BY(PERSONNE.Person_ID)
Finally i 'm a little happy to have arrive to resolve this problem of course with your help, but as i said i'm a beginner !
Thanks for all your time
Christophe
I think i will come often on this site because there are many article interesting and people are too strong in t sql 🙂 !
April 6, 2008 at 8:32 am
Keep in mind that for every distinct person returned a sub query will execute, in this case you have 3 sub queries. That means if you return 100,000 distinct rows from your main query, you will execute 300,000 sub queries. You should be aware that if you are returning lots of records this can degrade performance.
Since you only need to calculate the totals once, not one for each row returned, I would calculate them before the query and set the value to a variable. This way you only have to perform the query and aggregation one time. Also, I recommend you use ANSI standart JOINs to relate tables together. It is much easier to read and is a standard.
DECLARE @totExpInact INT,
@totForInact INT,
@totForActif INT
/** QUERY TABLE EXPERIENCE ACTIF = 0**/
SELECT @totExpInact = COUNT(EXPERIENCE.Personne)
FROM EXPERIENCE
WHERE PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 0
/** QUERY TABLE FORMATION Actif = 0 **/
SELECT @totForInact = COUNT(FORMATION.Person)
FROM FORMATION
WHERE PERSONNE.Person_ID = FORMATION.Person
AND FORMATION.Actif = 0
/** QUERY TABLE FORMATION actif = 1 **/
SELECT @totForActif = COUNT(FORMATION.Person)
FROM FORMATION
WHERE PERSONNE.Person_ID = FORMATION.Person
AND FORMATION.Actif = 1
/*===============================================
LOOK AT MY QUERY LENGTH NOW
===============================================*/
SELECT PERSONNE.Person_ID ,
COUNT(PERSONNE.Person_ID) AS totExpAct,
@totExpInact AS totExpInact,
@totForInact AS totForInact,
@totForActif AS totForActif
FROM PERSONNE
INNER JOIN EXPERIENCE
ON PERSONNE.Person_ID = EXPERIENCE.Personne
WHERE EXPERIENCE.Actif = 1
GROUP BY(PERSONNE.Person_ID)
April 7, 2008 at 1:54 am
Hello Adam,
I don't know how to thanks you, your are very strong in sql !
Really really thanks
Have a nice day..
Christophe
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply