An SQL Statistics

foryou

Hi! I work on an intranet site of a university(sqlserver2005) and I need to do statistics for example I need to count the students new in the semester 2 for each city: Here is the SQL query that selects the new students in semester 2:

WITH r1 AS (SELECT CAPITALISE_SEMESTRE_1.NUM_INSCRIPTION, CAPITALISE_SEMESTRE_1.CODE_SEMESTRE , CAPITALISE_SEMESTRE_1.ann_uni, SEMESTRE_1.LIBELLE_SEMESTRE_AR,
TYPE_RELATION_SEMESTRE_1.LIBELLE_TYPE_RELATION_SEMESTRE
FROM CAPITALISE_SEMESTRE AS CAPITALISE_SEMESTRE_1 INNER JOIN
SEMESTRE AS SEMESTRE_1 ON CAPITALISE_SEMESTRE_1.CODE_SEMESTRE = SEMESTRE_1.CODE_SEMESTRE INNER JOIN
TYPE_RELATION_SEMESTRE AS TYPE_RELATION_SEMESTRE_1 ON
CAPITALISE_SEMESTRE_1.CODE_TYPE_RELATION_SEMESTRE = TYPE_RELATION_SEMESTRE_1.CODE_TYPE_RELATION_SEMESTRE
WHERE (CAPITALISE_SEMESTRE_1.CODE_TYPE_RELATION_SEMESTRE = 4) AND (CAPITALISE_SEMESTRE_1.CODE_SEMESTRE = 2)),
r2 AS
(SELECT CAPITALISE_SEMESTRE.NUM_INSCRIPTION AS Expr1, CAPITALISE_SEMESTRE.CODE_SEMESTRE AS Expr2,
SEMESTRE.LIBELLE_SEMESTRE_AR AS Expr3,CAPITALISE_SEMESTRE.ann_uni AS Expr4
FROM CAPITALISE_SEMESTRE AS CAPITALISE_SEMESTRE INNER JOIN
SEMESTRE AS SEMESTRE ON CAPITALISE_SEMESTRE.CODE_SEMESTRE = SEMESTRE.CODE_SEMESTRE INNER JOIN
TYPE_RELATION_SEMESTRE AS TYPE_RELATION_SEMESTRE ON
CAPITALISE_SEMESTRE.CODE_TYPE_RELATION_SEMESTRE = TYPE_RELATION_SEMESTRE.CODE_TYPE_RELATION_SEMESTRE
WHERE (CAPITALISE_SEMESTRE.ann_uni = @ann_uni) AND (CAPITALISE_SEMESTRE.CODE_SEMESTRE = 1))
SELECT r1_1.NUM_INSCRIPTION, r1_1.ann_uni,r1_1.CODE_SEMESTRE, r1_1.LIBELLE_SEMESTRE_AR, r1_1.LIBELLE_TYPE_RELATION_SEMESTRE, r2_1.Expr1,
r2_1.Expr2, r2_1.Expr3, r2_1.Expr4
FROM r1 AS r1_1 INNER JOIN
r2 AS r2_1 ON r1_1.NUM_INSCRIPTION = r2_1.Expr1

but I do not know how to calculate the number for each city: Number of sudent new in semester2------------City 12222---------------------------------city1 123----------------------------------city2 Thanks.

Mycroft Holmes

Somewhere in that godawful load of crap you call a query there is a join to a table that has the city in it, use group by cityname may be useful. That is one ugly query!

Never underestimate the power of human stupidity RAH

foryou

Thanks Mycroft Holmes; Groupby gives me no count of student in any city

WITH r1
AS (SELECT CAPITALISE_SEMESTRE_1.NUM_INSCRIPTION,
CAPITALISE_SEMESTRE_1.CODE_SEMESTRE , CAPITALISE_SEMESTRE_1.CODE_TYPE_RELATION_SEMESTRE ,CAPITALISE_SEMESTRE_1.ann_uni

                     FROM         CAPITALISE\_SEMESTRE AS CAPITALISE\_SEMESTRE\_1 WHERE  (CAPITALISE\_SEMESTRE\_1.CODE\_TYPE\_RELATION\_SEMESTRE = 4) AND (CAPITALISE\_SEMESTRE\_1.CODE\_SEMESTRE = 2)),

r2 AS
(SELECT CAPITALISE_SEMESTRE.NUM_INSCRIPTION AS Expr1, CAPITALISE_SEMESTRE.CODE_SEMESTRE AS Expr2,
CAPITALISE_SEMESTRE.ann_uni AS Expr4
FROM CAPITALISE_SEMESTRE AS CAPITALISE_SEMESTRE

  WHERE      (CAPITALISE\_SEMESTRE.ann\_uni = '2008/2009') AND (CAPITALISE\_SEMESTRE.CODE\_SEMESTRE = 1))
SELECT     r1\_1.NUM\_INSCRIPTION
 FROM         r1 AS r1\_1 INNER JOIN
                        r2 AS r2\_1 ON r1\_1.NUM\_INSCRIPTION = r2\_1.Expr1