Requete (im)possible ?

Bonjour,

[ Olivier ] a écrit:

j'ai une table [LESSONS] avec

date_lesson DATETIME -- la date du cours
type_lesson INT - le type de cours (maths anglais etc..)
student_status INT -- étudiant présent ou absent

je voudrais savoir pour une année donnée et par mois :
- combien de cours y a t'il eu en général
- combien d'absence y a t'il eu en général
- combien de cours y a t'il eu pour chaque type

Il nous manque la clé primaire de la table... Peux-tu poster le code de
création de LESSONS ? Si je comprends bien, c'est une table de lien
entre un cours et des étudiants ?

Basiquement :

-- combien de cours
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
COUNT(DISTINCT lesson_id)
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson)

-- combien d'absences
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois, COUNT(*)
FROM LESSONS
WHERE student_status = ???
GROUP BY YEAR(date_lesson), MONTH(date_lesson)

-- combien de cours par type
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
type_lesson, COUNT(DISTINCT lesson_id)
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson), type_lesson


--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT, MVP SQL Server
http://www.babaluga.com/
http://rudi.developpez.com/

On 25 jan, 10:20, Rudi Bruchez <rudi#nospam#at#babaluga.com> wrote:

Bonjour,

[ Olivier ] a écrit:

> j'ai une table [LESSONS] avec

> date_lesson DATETIME -- la date du cours
> type_lesson INT - le type de cours (maths anglais etc..)
> student_status INT -- étudiant présent ou absent

> je voudrais savoir pour une année donnée et par mois :
> - combien de cours y a t'il eu en général
> - combien d'absence y a t'il eu en général
> - combien de cours y a t'il eu pour chaque type

Il nous manque la clé primaire de la table... Peux-tu poster le code de
création de LESSONS ? Si je comprends bien, c'est une table de lien
entre un cours et des étudiants ?

Basiquement :

-- combien de cours
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
COUNT(DISTINCT lesson_id)
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson)

-- combien d'absences
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois, COUNT(*)
FROM LESSONS
WHERE student_status = ???
GROUP BY YEAR(date_lesson), MONTH(date_lesson)

-- combien de cours par type
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
type_lesson, COUNT(DISTINCT lesson_id)
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson), type_lesson

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT, MVP SQL Serverhttp://www.babalu ga.com/http://rudi.developpez.com/

merci mais j'ai oublié de préciser que je reverai d'avoir tous ces
retours avec une seule requete

genre :

Mois : Nombre Cours : Nombres Absences : TypeCour1 : Type Cour2 :
Janvier : 120 : 6 : 114 : 6
Février : 168 : 14 : 150 : 18
etc...
etc...

[ Olivier ] a écrit:

Il nous manque la clé primaire de la table... Peux-tu poster le code de
création de LESSONS ? Si je comprends bien, c'est une table de lien
entre un cours et des étudiants ?

Peux-tu répondre à la question stp ?

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT, MVP SQL Server
http://www.babaluga.com/
http://rudi.developpez.com/

le voila ci dessous.
merci

--"""""""""""""""""""""""""""""""""""""
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
01/26/2008 17:04:42 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB
, FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ecole] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ecole] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ecole] SET ARITHABORT OFF
GO
ALTER DATABASE [ecole] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ecole] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ecole] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ecole] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ecole] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ecole] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ecole] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ecole] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ecole] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ecole] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ecole] SET ENABLE_BROKER
GO
ALTER DATABASE [ecole] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ecole] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ecole] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ecole] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ecole] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ecole] SET READ_WRITE
GO
ALTER DATABASE [ecole] SET RECOVERY FULL
GO
ALTER DATABASE [ecole] SET MULTI_USER
GO
ALTER DATABASE [ecole] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ecole] SET DB_CHAINING OFF



USE [ecole]
GO
/****** Objet : Table [dbo].[students] Date de génération du script :
01/26/2008 17:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [students](
[student_ID] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED
(
[student_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Objet : Table [dbo].[lessons] Date de génération du script :
01/26/2008 17:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [lessons](
[lesson_id] [int] IDENTITY(1,1) NOT NULL,
[lesson_name] [nchar](100) NULL,
CONSTRAINT [PK_lessons] PRIMARY KEY CLUSTERED
(
[lesson_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Objet : Table [dbo].[status] Date de génération du script :
01/26/2008 17:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [status](
[status_id] [int] IDENTITY(1,1) NOT NULL,
[status_name] [nchar](100) NULL,
CONSTRAINT [PK_status] PRIMARY KEY CLUSTERED
(
[status_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Objet : Table [dbo].[courses] Date de génération du script :
01/26/2008 17:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [courses](
[course_id] [int] IDENTITY(1,1) NOT NULL,
[date_lesson] [datetime] NULL,
[student_id] [int] NULL,
[status_id] [int] NULL,
[lesson_id] [int] NULL,
CONSTRAINT [PK_courses] PRIMARY KEY CLUSTERED
(
[course_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO









INSERT INTO students (nom) VALUES ('Marc');
INSERT INTO students (nom) VALUES ('Paul');

INSERT INTO [status] (status_name) VALUES ('Absent');
INSERT INTO [status] (status_name) VALUES ('Excusé');

INSERT INTO lessons (lesson_name) VALUES ('Anglais');
INSERT INTO lessons (lesson_name) VALUES ('Math');

INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('01/01/2007',1,NULL,1);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('02/01/2007',1,1,1);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('01/02/2007',1,NULL,2);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('02/02/2007',1,NULL,1);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('03/02/2007',1,NULL,1);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('03/03/2007',1,NULL,2);



INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('03/01/2007',2,NULL,2);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('03/03/2007',2,1,1);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('03/06/2007',2,NULL,1);
INSERT INTO courses (date_lesson, student_id, status_id, lesson_id) VALUES
('09/06/2007',2,NULL,1);









-- FIN DE SCRIPT *********************







"Rudi Bruchez" <rudi#nospam#at#babaluga.com> a écrit dans le message de
news: O9$eEoCYIHA.3652@TK2MSFTNGP02.phx.gbl...

[ Olivier ] a écrit:

Il nous manque la clé primaire de la table... Peux-tu poster le code de
création de LESSONS ? Si je comprends bien, c'est une table de lien
entre un cours et des étudiants ?

Peux-tu répondre à la question stp ?

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT, MVP SQL Server
http://www.babaluga.com/
http://rudi.developpez.com/

Quelque chose comme :

SELECT *
FROM (SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
COUNT(DISTINCT lesson_id) AS NB_LESSONS,
SUM(CASE WHEN student_status = ??? THEN 1 ELSE 0) AS
NB_ABSENCES
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson)) T1
FULL OUTER JOIN (SELECT YEAR(date_lesson) as Annee,
MONTH(date_lesson) as Mois,
type_lesson, COUNT(DISTINCT lesson_id)
AS NB_LECONS
FROM LESSONS
GROUP BY YEAR(date_lesson),
MONTH(date_lesson), type_lesson) T2
ON T1.Annee = T2.Annee
AND T1.Mois = T2.Mois

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
*********************** http://www.sqlspot.com *************************



[ Olivier ] a écrit :

On 25 jan, 10:20, Rudi Bruchez <rudi#nospam#at#babaluga.com> wrote:

Bonjour,

[ Olivier ] a écrit:

j'ai une table [LESSONS] avec
date_lesson DATETIME -- la date du cours
type_lesson INT - le type de cours (maths anglais etc..)
student_status INT -- étudiant présent ou absent
je voudrais savoir pour une année donnée et par mois :
- combien de cours y a t'il eu en général
- combien d'absence y a t'il eu en général
- combien de cours y a t'il eu pour chaque type

Il nous manque la clé primaire de la table... Peux-tu poster le code de
création de LESSONS ? Si je comprends bien, c'est une table de lien
entre un cours et des étudiants ?

Basiquement :

-- combien de cours
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
COUNT(DISTINCT lesson_id)
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson)

-- combien d'absences
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois, COUNT(*)
FROM LESSONS
WHERE student_status = ???
GROUP BY YEAR(date_lesson), MONTH(date_lesson)

-- combien de cours par type
SELECT YEAR(date_lesson) as Annee, MONTH(date_lesson) as Mois,
type_lesson, COUNT(DISTINCT lesson_id)
FROM LESSONS
GROUP BY YEAR(date_lesson), MONTH(date_lesson), type_lesson

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT, MVP SQL Serverhttp://www.babaluga.com/http://rudi.developpez.com/

merci mais j'ai oublié de préciser que je reverai d'avoir tous ces
retours avec une seule requete

genre :

Mois : Nombre Cours : Nombres Absences : TypeCour1 : Type Cour2 :
Janvier : 120 : 6 : 114 : 6
Février : 168 : 14 : 150 : 18
etc...
etc...