Discussion :

[cannelline]

bonjour a tous,

J'avais un script (JSP) ou je faisais plusieurs requetes avec des boucles sur les resultats pour calculer des trucs... Et maintenant j'utilise un outil de report qui m'oblige a tout rassembler dans une seule requete... Sauf qu'avec le SQL je me fais vite des noeuds au cerveau quand ca devient un peu plus compliqué...

Peut-etre pourrez-vous m'aider a y voir un peu plus clair ?

tout d'abord mes tables :

departement (id - nom_dep)
cours_departement (id_cours - id_dep)
cours (id_cours - id_semestre)
activity (id_cours - ....)
cours_user (id_cours - id_user - roles...)

mon script :

je recupere tous les departements.
pour chaque departement je recupere la liste des cours attachés
pour chaque cours
je compte combien de records j'ai dans la table activity concernant ce cours
je compte combien de records j'ai dans la table cours_user concernant ce cours et dont le role du user est 'G'

j'additionne les valeurs 'activity' de tous les cours d'un departement
j'additionne les valeurs 'cours_user' de tous les cours d'un departement

a la fin, j'ai un tableau me donnant ces informations :

nom du departement - nombre de cours dans ce departement - total de l'activite pour ce dep - total des utilisateurs avec le role 'G' pour ce dep

voici la requete unique qui, je pensais, correspondait a ce script, mais je ne maitrise pas les fonctions de somme ou autre donc ca ne fonctionne pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
select d.nom_dep, count(d.id_dep), sum(c1.count(*)), sum(c2.count(*)) 
from departement d, 
		cours_departement cd, 
		cours c, 
		(select id_cours  , count(*) from activity  group by id_cours ) c1,  
		(select id_cours , count(*) from cours_users   where ROLE='G' group by id_cours ) c2  
where c.id_cours  = c1.id_cours 
		and c.id_cours = c2.id_cours 
		and cd.id_dep  = d.id_dep 
		and cd.id_cours = c.id_cours  
group by d.id_dep

ca a l'air de bloquer au niveau des sommes... any idea ?

[skuatamad]

Salut,

Je crois pas que tu puisses faire un sum(count(*)), mais en utilisant un alias au moins ça ne "bloquera" plus (si j'ai bien compris ce que tu voulais):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT d.nom_dep, count(d.id_dep), sum(c1.nb1), sum(c2.nb2) 
FROM departement d, 
		cours_departement cd, 
		cours c, 
		(SELECT id_cours  , count(*) as nb1 FROM activity  GROUP BY id_cours ) c1,  
		(SELECT id_cours , count(*) as nb2 FROM cours_users   WHERE ROLE='G' GROUP BY id_cours ) c2  
WHERE c.id_cours  = c1.id_cours 
		AND c.id_cours = c2.id_cours 
		AND cd.id_dep  = d.id_dep 
		AND cd.id_cours = c.id_cours  
GROUP BY d.id_dep

[cannelline]

en effet, ca ne bloque plus... merci !

il faut maintenant que je verifie si j'obtiens le meme resultat que mon script... ca j'en suis pas sure...

[cannelline]

Eh bien je n'ai pas du tout le meme resultat...

voici mon script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
my $dptquery = "SELECT pk1, batch_uid, title FROM gc ORDER BY title";
my $dptqh = $dbh->prepare($dptquery);
$dptqh->execute();
print "Dept Code\tDept Title\tNumber of Sections\tActivated\tNumber of Accesses\n";
while (my($dptpk, $batch_uid, $title) = $dptqh->fetchrow_array()) {
  my $course_cat_q = "SELECT gcc.crsmain_pk1, cm.course_id FROM gcc, cm WHERE gcc.gatewaycat_pk1=$dptpk AND gcc.crsmain_pk1=cm.pk1";
  my $ccqh = $dbh->prepare($course_cat_q);
  $ccqh->execute();
  my $dept_count = 0;
  my $acc_count = 0;
  my $active_count = 0;
  while (my ($crsmain_pk1, $course_id) = $ccqh->fetchrow_array()) {
    $dept_count++;
    my $access_query = "SELECT count(1) FROM aa WHERE course_pk1=$crsmain_pk1";
    my $acqh = $dbh->prepare($access_query);
    $acqh->execute();
    my ($accesses) = $acqh->fetchrow_array();
    $acc_count+=$accesses;
    $acqh->finish();
    my $active_query = "SELECT count(1) FROM cu WHERE crsmain_pk1=$crsmain_pk1 AND (role=\'P\' OR role=\'G\')";
    my $activeqh = $dbh->prepare($active_query);
    $activeqh->execute();
    my $instructors = 0;
    ($instructors) = $activeqh->fetchrow_array();
    if ($instructors > 0) {
      $active_count++;
    }
    $activeqh->finish();
  }
  $ccqh->finish();
  if ($acc_count > 0) {
    print "$batch_uid\t$title\t\t$dept_count\t$active_count\t$acc_count\n";
  }
}

comment obtenir le meme resultat avec une seule requete ?

merci de votre aide.

[skuatamad]

Pourrais tu nous fournir un jeu de test avec le résultat souhaité stp, ce sera beaucoup plus facile pour nous

[cannelline]

ok, je vais essayer.

table gc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
pk1   batch_uid   title
1      009            nom1
2      012            nom2
3      025            nom3

table gcc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
gatewaycat_pk1           crsmain_pk1
1                                 235
1                                 531
2                                 123
2                                 953
2                                 056
3                                 463

table cm :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
pk1                        course_id
1                           'english002'
2                           'english004'
3                           'maths012'
4                           'history003'

table aa : (table enorme avec beaucoup de lignes par cours)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
course_pk1     type_acces
1                    blabla
1                    blabla
1                    blabla
1                    blabla
2                    blabla
2                    blabla
2                    blabla
2                    blabla

table cu :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
crsmain_pk1        user_pk1        role
1                         456123          'G'
1                         489               'S'
2                         ...                 'P'
...

resultat que je veux obtenir :

[code dept (gc.batch_uid)] [nom du dept(gc.title)] [nb de cours par dept] [nb de cours actifs] [nombre d'acces a ce cours]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Dept Code	Dept Title			Number of Sections	Activated	Number of Accesses
009		Accounting			53			24		3099
023		Africana Studies		50			10		52
088		Agriculture/Natural Resources	25			6		12
099		American Studies		21			10		5
113		Animal Science			60			17		4
122		Anthropology			335			46		15
133		Architecture			141			2		21

le nombre de cours actifs pour un dept est calculé en additionnant le nombre de cours de ce departement qui a au moins une ligne dans la table 'cu' avec un role P ou G

le nombre d'acces pour un dept est calculé en additionnant le resultat de la requete

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT count(1) FROM aa WHERE course_pk1=$crsmain_pk1

pour chaque cours de ce dept.

j'espere que c'est pas trop fouilli...