somme de records

Fred a utilisé son clavier pour écrire :

Bonjour,
J'ai une requête qui se présente comme ceci :

select service_req.request_user, service_req.title
from service_req
inner join sysaid_user
on service_req.request_user = sysaid_user.user_name
inner join company
on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
order by service_req.request_user

Ce qui me donne comme résultat :

User 1 pc crashed
User 1 install office
User 2 network cable
User 2 create special GPO
User 3 problem x
User 3 problem y
User 3 problem z


J'aimerais, peu importe sous quelle forme (colonne supplémentaire,
sous-total, ...), avoir le nombre de calls ouverts par User ...

Est-ce possible ?

D'avance merci,

Quelque chose comme :

select service_req.request_user, count(*) as NB
from service_req
inner join sysaid_user
on service_req.request_user = sysaid_user.user_name
inner join company
on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
group by service_req.request_user

--

Dominique

Hello,
Merci mais ne peut-on pas garder le title dans la requête car c'est une info
importante ?
Merci d'avance.

"TedIF" a écrit :

Fred a utilisé son clavier pour écrire :
> Bonjour,
> J'ai une requête qui se présente comme ceci :
>
> select service_req.request_user, service_req.title
> from service_req
> inner join sysaid_user
> on service_req.request_user = sysaid_user.user_name
> inner join company
> on sysaid_user.company = company.company_id
> where company_id = 1
> and service_req.insert_time > '2009-01-31 23:59:59'
> and service_req.insert_time < '2009-03-01 00:00:00'
> order by service_req.request_user
>
> Ce qui me donne comme résultat :
>
> User 1 pc crashed
> User 1 install office
> User 2 network cable
> User 2 create special GPO
> User 3 problem x
> User 3 problem y
> User 3 problem z
>
>
> J'aimerais, peu importe sous quelle forme (colonne supplémentaire,
> sous-total, ...), avoir le nombre de calls ouverts par User ...
>
> Est-ce possible ?
>
> D'avance merci,

Quelque chose comme :

select service_req.request_user, count(*) as NB
from service_req
inner join sysaid_user
on service_req.request_user = sysaid_user.user_name
inner join company
on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
group by service_req.request_user

--

Dominique

Fred avait énoncé :

Hello,
Merci mais ne peut-on pas garder le title dans la requête car c'est une info
importante ?
Merci d'avance.

Pour avoir le nombre d'appel par personne et par titre :

select service_req.request_user,
count(*) as NB,
service_req.title
from service_req
inner join sysaid_user on service_req.request_user =
said_user.user_name
inner join company on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
group by service_req.request_user, service_req.title

ou bien pour avoir la liste des appels avec le cumul d'appel par
personne :

select service_req.request_user,
service_req.title,
NB_CALL.NB
from service_req
inner join sysaid_user on service_req.request_user =
said_user.user_name
inner join company on sysaid_user.company = company.company_id
left outer join (

select service_req.request_user,
count(*) as NB
from service_req
inner join sysaid_user on service_req.request_user =
said_user.user_name
inner join company on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
group by service_req.request_user

) NB_CALL ON NB_CALL.request_user = service_req.request_user
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'

--

Dominique

Plus simple :

1) avant 2005 :

select SR.request_user, SR.title,
(SELECT COUNT(*)
FROM service_req AS SR2
inner join sysaid_user AS SU2
on SR2.request_user = SU2.user_name
WHERE SU2.company = SU.company
AND SR2.request_user = SR.request_user
AND SR2.insert_time > '2009-01-31 23:59:59'
AND SR2.insert_time < '2009-03-01 00:00:00') AS NB
from service_req AS SR
inner join sysaid_user AS SU
on SR.request_user = SU.user_name
inner join company AS C
on SU.company = C.company_id
where company_id = 1
and SR.insert_time > '2009-01-31 23:59:59'
and SR.insert_time < '2009-03-01 00:00:00'
order by SR.request_user

Cette requête pourrait être simplifié et optimisée sir vus nous aviez
donné la description de vos tables ous forme DDL et notamment la clef de
chacune des tables.

2) a partir de 2005 :
select SR.request_user, SR.title,
COUNT(*) OVER(PARTITION BY SR.request_user) AS NB
from service_req AS SR
inner join sysaid_user AS SU
on SR.request_user = SU.user_name
inner join company AS C
on SU.company = C.company_id
where company_id = 1
and SR.insert_time > '2009-01-31 23:59:59'
and SR.insert_time < '2009-03-01 00:00:00'
order by SR.request_user

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies
*********************** http://www.sqlspot.com *************************


Fred a écrit :

Bonjour,
J'ai une requête qui se présente comme ceci :

select service_req.request_user, service_req.title
from service_req
inner join sysaid_user
on service_req.request_user = sysaid_user.user_name
inner join company
on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
order by service_req.request_user

Ce qui me donne comme résultat :

User 1 pc crashed
User 1 install office
User 2 network cable
User 2 create special GPO
User 3 problem x
User 3 problem y
User 3 problem z


J'aimerais, peu importe sous quelle forme (colonne supplémentaire,
sous-total, ...), avoir le nombre de calls ouverts par User ...

Est-ce possible ?

D'avance merci,

Aucun des 2 résultats ne correspond à ce que je voulais mais merci quand même
:)

"TedIF" a écrit :

Fred avait énoncé :
> Hello,
> Merci mais ne peut-on pas garder le title dans la requête car c'est une info
> importante ?
> Merci d'avance.
>


Pour avoir le nombre d'appel par personne et par titre :

select service_req.request_user,
count(*) as NB,
service_req.title
from service_req
inner join sysaid_user on service_req.request_user =
said_user.user_name
inner join company on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
group by service_req.request_user, service_req.title

ou bien pour avoir la liste des appels avec le cumul d'appel par
personne :

select service_req.request_user,
service_req.title,
NB_CALL.NB
from service_req
inner join sysaid_user on service_req.request_user =
said_user.user_name
inner join company on sysaid_user.company = company.company_id
left outer join (

select service_req.request_user,
count(*) as NB
from service_req
inner join sysaid_user on service_req.request_user =
said_user.user_name
inner join company on sysaid_user.company = company.company_id
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'
group by service_req.request_user

) NB_CALL ON NB_CALL.request_user = service_req.request_user
where company_id = 1
and service_req.insert_time > '2009-01-31 23:59:59'
and service_req.insert_time < '2009-03-01 00:00:00'

--

Dominique