Compter nombre de lignes

Version imprimable

Bonjour,

J'ai une table Profile, Projet et Items

A savoir qu'un profile a 1 ou n Projet et qu'un projet a 1 ou n items

Je souhaite avoir dans une même requête le nombre d'items par projet et le nombre de projet par profile.

Quand je fais la requete suivante
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT pf.ProfileId, pj.ProjectId, it.ItemsId FROM dbo.Profiles AS pf LEFT OUTER JOIN dbo.Projects AS pj ON pf.ProfileId = pj.ProfileIdFK LEFT OUTER JOIN dbo.Items AS it ON pj.ProjectId = it.ProjectIdFK GROUP BY pf.ProfileId
J'ai le résultat suivant
ProfileId----------ProjectId----------ItemsId
62-----------------15-------------------23
62-----------------15-------------------21
65-----------------16-------------------24
66----------------NULL----------------NULL

Maintenant je fais la requete suivante pour avoir le nombre d'items par projet
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT pf.ProfileId, pj.ProjectId, COUNT (it.ItemsId) AS ["Nombre d'items"] FROM dbo.Profiles AS pf LEFT OUTER JOIN dbo.Projects AS pj ON pf.ProfileId = pj.ProfileIdFK LEFT OUTER JOIN dbo.Items AS it ON pj.ProjectId = it.ProjectIdFK GROUP BY pf.ProfileId, pj.ProjectId
J'ai le résultat suivant
ProfileId----------ProjectId----------Nombre d'Items
----62-----------------15---------------------2-----------
----65-----------------16---------------------1-----------
----66----------------NULL------------------NULL-------

Etmaintenant je souhaite connaitre le nombre de projet avec le nombre d'items par profile, c'est ici que je fais pas la bonne requete, car il me compte 2 fois le projet 15, ce qui est normal parce que je compte aussi le nombre d'items.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT pf.ProfileId, COUNT(pj.ProjectId) AS ["Nombre de Projets"], COUNT (it.ItemsId) AS ["Nombre d'Items"] FROM dbo.Profiles AS pf LEFT OUTER JOIN dbo.Projects AS pj ON pf.ProfileId = pj.ProfileIdFK LEFT OUTER JOIN dbo.Items AS it ON pj.ProjectId = it.ProjectIdFK GROUP BY pf.ProfileId
Le résultat est le suivant
ProfileId----------Nombre projets----------Nombre d'Items
----62--------------------2-----------------------------2-----------
----65--------------------1-----------------------------1-----------
----66-----------------NULL-------------------------NULL-------

Je cherche donc à avoir pour le profile 62 1 seul projet avec ses 2 items

Merci d'avance

12/11/2010, 14h53
dublow

Reflexion

Bon!!!, il suffit d'utiliser son cerveau et d'aller sur la doc msdn (transact sql) et de lire ça : COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

Rien de plus à dire......
12/11/2010, 16h10
SQLpro

Citation:

Envoyé par dublow

Bon!!!, il suffit d'utiliser son cerveau et d'aller sur la doc msdn (transact sql) et de lire ça : COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

Rien de plus à dire......

Si : que cela fait partie du langage SQL depuis plus de 30 ans !!!!!!!
Il suffit donc de lire n'importe quel bon site web ou n'importe quel bon bouquin sur le sujet et d'apprendre le langage SQL....
Mon site web, comme mon bouquin est fait pour ça !

A +
12/11/2010, 17h42
dublow

Merci pour la critique, mais pour ce coup là j'ai pas vraiment reflechi.
J'ai une question à propos de ma requête.
J'ai interrogé 3 tables ou j'ai pu comptabiliser le nombre de projets et le nombre d'items.
J'ai une table ("News") qui contient les informations sur les profiles, et une table newsType qui contient le type de news.

la table NewsType contient les données suivantes
NewsTypeId----------Name
-----1-------------------Aime
-----2-------------------Abonné
-----3-------------------Publié

Voici un exemple des données de la table news

ItemsIdFK----------NewsTypeIdFK----------ProfileIdFK----------ProfileFollowerIdFK
----15------------------------1------------------------62------------------------NULL--------
----NULL---------------------2------------------------65-------------------------62----------

Dans cette exemple, le profil 62 aime l'item 15 et le profil 62 est abonné au profil 65

Je souhaite savoir combien de fois un profile a aimer qque chose ou combien de profil il suit....
Faire la requete seule reste accessible mais la combiner avec la requete precedente me pose problème.

Est ce que tu vois ce que je veux faire?
Je cherche des pistes....

Merci d'avance
12/11/2010, 18h53
SQLpro

Vous pouvez encapsuler plusieurs COUNT, SUM, MAX, MIN, AVG différent portant sur des groupages différents en utilisant les fonctions de fenêtrage.
Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/article...clause-window/

Quelque chose comme :
SELECT COUNT(..) OVER(PARTITION BY ...),
COUNT(..) OVER(PARTITION BY ...),
...

Difficile devous aider plus sans le respect de la charte de postage qui vous demande de poster le DDL de vos tables et un jeu d'essais sous forme INSERT :
http://www.developpez.net/forums/d96...vement-poster/

A +

Merci pour votre réponse,

J'ai réouvert votre livre (SQL 3eme edition, PEARSON). est ce que les sous requêtes corrélées (p.166) peuvent répondre à ma question?

Je met le script nécessaire

Code:

1
2
3
4
5
6
7
8
CREATE TABLE [dbo].[NewsTypes](
	[NewsTypeId] [smallint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_NewsTypeId] PRIMARY KEY NONCLUSTERED 
(
	[NewsTypeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Code:

1
2
3
4
5
INSERT [dbo].[NewsTypes] ( [Name]) VALUES (N's''est abonné')
INSERT [dbo].[NewsTypes] ( [Name]) VALUES (N'a publié')
INSERT [dbo].[NewsTypes] ( [Name]) VALUES (N'a crée')
INSERT [dbo].[NewsTypes] ( [Name]) VALUES (N'aime')
INSERT [dbo].[NewsTypes] ( [Name]) VALUES (N'a commenté')

Code:

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
36
37
38
39
40
41
42
CREATE TABLE [dbo].[Profiles](
	[ProfileId] [bigint] IDENTITY(1,1) NOT NULL,
	[CountryIdFK] [smallint] NOT NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[Alias] [varchar](50) NULL,
	[Sex] [smallint] NULL,
	[Birthdate] [datetime] NULL,
	[Password] [varchar](50) NOT NULL,
	[Address] [varchar](255) NULL,
	[PostalCode] [varchar](10) NULL,
	[City] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[Email] [varchar](70) NOT NULL,
	[PhonePrefix] [varchar](5) NULL,
	[PhoneNumber] [varchar](20) NULL,
	[NumberInvitations] [smallint] NULL,
	[IsCreative] [bit] NULL,
	[CreatedDate] [datetime] NOT NULL,
	[Description] [varchar](255) NULL,
	[LastConnection] [datetime] NULL,
	[IsFullScreen] [bit] NULL,
	[HasActivate] [bit] NULL,
	[FacebookId] [nvarchar](100) NULL,
	[TwitterId] [nvarchar](100) NULL,
	[WebSiteAddress] [nvarchar](100) NULL,
	[UrlProfile] [nvarchar](100) NULL,
	[IsFavorite] [bit] NULL,
	[IsMarked] [bit] NULL,
	[IsSelected] [bit] NULL,
	[UpdateDate] [datetime] NULL,
	[ProjectIdFK] [int] NULL,
	[LanguageIdFK] [int] NULL,
	[WatermarkIdFK] [int] NULL,
	[NumberSentInvitation] [smallint] NULL,
	[AvatarIdFK] [bigint] NULL,
	[BackgroundIdFK] [bigint] NULL,
 CONSTRAINT [PK_PROFILES] PRIMARY KEY NONCLUSTERED 
(
	[ProfileId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Code:

1
2
3
4
 
INSERT [dbo].[Profiles] ( [CountryIdFK], [LastName], [FirstName], [Alias], [Sex], [Birthdate], [Password], [Address], [PostalCode], [City], [State], [Email], [PhonePrefix], [PhoneNumber], [NumberInvitations], [IsCreative], [CreatedDate], [Description], [LastConnection], [IsFullScreen], [HasActivate], [FacebookId], [TwitterId], [WebSiteAddress], [UrlProfile], [IsFavorite], [IsMarked], [IsSelected], [UpdateDate], [ProjectIdFK], [LanguageIdFK], [WatermarkIdFK], [NumberSentInvitation], [AvatarIdFK], [BackgroundIdFK]) VALUES ( 1, N'name1', N'Nico', N'heyehy', 0, CAST(0x0000725A00000000 AS DateTime), N'mypassword', N'12, rue Pierre et Marie Curie', N'91000', N'city1', NULL, N'email@gmail.com', N'+33', N'649314938', 72, 1, CAST(0x00009F8B00000000 AS DateTime), N'toto et tata', CAST(0x00009E1E00000000 AS DateTime), 1, 1, N'vrevrfe', N'http://www.twitter.com/56437890', N'jnjknze', N'http://www.blabla.com/567832456890', 1, 1, 0, CAST(0x00009E0C00000000 AS DateTime), NULL, 39, NULL, NULL, 10, NULL)
INSERT [dbo].[Profiles] ( [CountryIdFK], [LastName], [FirstName], [Alias], [Sex], [Birthdate], [Password], [Address], [PostalCode], [City], [State], [Email], [PhonePrefix], [PhoneNumber], [NumberInvitations], [IsCreative], [CreatedDate], [Description], [LastConnection], [IsFullScreen], [HasActivate], [FacebookId], [TwitterId], [WebSiteAddress], [UrlProfile], [IsFavorite], [IsMarked], [IsSelected], [UpdateDate], [ProjectIdFK], [LanguageIdFK], [WatermarkIdFK], [NumberSentInvitation], [AvatarIdFK], [BackgroundIdFK]) VALUES ( 1, N'name 2', N'toti', N'jiji', 0, CAST(0x0000725A00000000 AS DateTime), N'mypassword', N'frjkfrznf', N'69118', N'ville 4', NULL, N'nic@gmail.com', N'+33', N'646314938', 10, 1, CAST(0x00009E1E00000000 AS DateTime), N'Profil de Nicolas', CAST(0x00009E1E00000000 AS DateTime), 1, 1, N'evfgrvg', N'http://www.twitter.com/56437890', N'http://www.nicolas.fr', N'http://www.wheee.com/567832456890', 1, 0, 1, CAST(0x00009E0C00000000 AS DateTime), NULL, 39, NULL, NULL, 10, NULL)
INSERT [dbo].[Profiles] ( [CountryIdFK], [LastName], [FirstName], [Alias], [Sex], [Birthdate], [Password], [Address], [PostalCode], [City], [State], [Email], [PhonePrefix], [PhoneNumber], [NumberInvitations], [IsCreative], [CreatedDate], [Description], [LastConnection], [IsFullScreen], [HasActivate], [FacebookId], [TwitterId], [WebSiteAddress], [UrlProfile], [IsFavorite], [IsMarked], [IsSelected], [UpdateDate], [ProjectIdFK], [LanguageIdFK], [WatermarkIdFK], [NumberSentInvitation], [AvatarIdFK], [BackgroundIdFK]) VALUES ( 1, N'AZERTY', N'Noo', N'pht', 0, CAST(0x0000725A00000000 AS DateTime), N'mypassword', N'25, rue Pierre et Maris Curie', N'69000', N'Lyon', NULL, N'dfr@gmail.com', N'+33', N'646314938', 10, 1, CAST(0x0000A0F900000000 AS DateTime), N'Profil de fifi', CAST(0x00009E1E00000000 AS DateTime), 1, 1, N'fgtr', N'http://www.twitter.com/56437890', N'http://www.nicolas.fr', N'http://www.hoho.com/567832456890', 0, 0, 0, CAST(0x00009E0C00000000 AS DateTime), NULL, 39, NULL, NULL, 10, NULL)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE [dbo].[News](
	[NewsId] [bigint] IDENTITY(1,1) NOT NULL,
	[NewsTypeIdFK] [smallint] NOT NULL,
	[ItemsIdFK] [bigint] NULL,
	[ProfileIdFK] [bigint] NULL,
	[ProfileIdFollowerFK] [bigint] NULL,
	[Date] [datetime] NOT NULL,
 CONSTRAINT [PK_NewsId] PRIMARY KEY NONCLUSTERED 
(
	[NewsId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Code:

1
2
3
 
INSERT [dbo].[News] ( [NewsTypeIdFK], [ItemsIdFK], [ProfileIdFK], [ProfileIdFollowerFK], [Date]) VALUES ( 4, NULL, 62, NULL, CAST(0x00009E1600000000 AS DateTime))
INSERT [dbo].[News] ( [NewsTypeIdFK], [ItemsIdFK], [ProfileIdFK], [ProfileIdFollowerFK], [Date]) VALUES ( 1, NULL, 62, 65, CAST(0x00009E1600000000 AS DateTime))

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE [dbo].[Items](
	[ItemsId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProjectIdFK] [bigint] NOT NULL,
	[WatermarkIdFK] [int] NULL,
	[ImageIdFK] [bigint] NULL,
	[Name] [varchar](50) NOT NULL,
	[IsPublishedOnProject] [bit] NOT NULL,
	[UpdateDate] [datetime] NULL,
	[PublishDate] [datetime] NULL,
	[PortfolioIdFK] [bigint] NULL,
	[NumberLiked] [int] NULL,
	[NumberSeen] [int] NULL,
	[NumberComments] [int] NULL,
	[NumberShareFacebook] [int] NULL,
	[NumberShareTwitter] [int] NULL,
	[NumberShareMail] [int] NULL,
	[IsFavorite] [bit] NULL,
	[IsMarked] [bit] NULL,
	[IsSelected] [bit] NULL,
 CONSTRAINT [PK_ItemsId] PRIMARY KEY NONCLUSTERED 
(
	[ItemsId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Code:

1
2
 
INSERT [dbo].[Items] ( [ProjectIdFK], [WatermarkIdFK], [ImageIdFK], [Name], [IsPublishedOnProject], [UpdateDate], [PublishDate], [PortfolioIdFK], [NumberLiked], [NumberSeen], [NumberComments], [NumberShareFacebook], [NumberShareTwitter], [NumberShareMail], [IsFavorite], [IsMarked], [IsSelected]) VALUES ( 15, NULL, 11, N'Artwork', 1, CAST(0x00009E0C00000000 AS DateTime), CAST(0x00009E0C00000000 AS DateTime), 7, 0, 0, 0, 0, 0, 0, 0, 1, 0)

Les données de la table news ne seront pas bonne, il faudrait les changer par rapport au Id généré sur les autres tables.

j'ai oublié une table...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE [dbo].[Projects](
	[ProjectId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProfileIdFK] [bigint] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[IsPublishedOnPortfolio] [bit] NOT NULL,
	[SortOrderOnPortfolio] [int] NOT NULL,
	[ImageIdFK] [bigint] NULL,
 CONSTRAINT [PK_ProjectId] PRIMARY KEY NONCLUSTERED 
(
	[ProjectId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Code:

1
2
INSERT [dbo].[Projects] ( [ProfileIdFK], [Name], [IsPublishedOnPortfolio], [SortOrderOnPortfolio], [ImageIdFK]) VALUES (62, N'Project Nicolas', 0, 1, NULL)
INSERT [dbo].[Projects] ( [ProfileIdFK], [Name], [IsPublishedOnPortfolio], [SortOrderOnPortfolio], [ImageIdFK]) VALUES (62, N'Project Paris', 1, 2, NULL)

Je me rend compte que les données ne vont pas être bonnes entre l'id du profile (FK) et le profile de la table profile

je vais trouver une meilleure solution en evitant l'identity...

12/11/2010, 22h11
SQLpro

Au final que voulez-vous obtenir comme réponse ?

Oui, vous pouvez utiliser les sous requêtes corrélées, mais les fonctions fenêtrées sont souvent beaucoup plus peformantes et plus simple à écrire.

A +
13/11/2010, 12h40
dublow

Bonjour,

je veux faire un tableau qui liste les données des profils

--LastName---FirstName---....ect---Nbe de projet---Nbe d'items---Aime---Aimé

De Lastname à nbe de projets, la requête est faite mais de Aime à aimé je n'arrive pas à compléter cette requête (je sais pas si je suis clair dans mon explication...)

De lastname à nbre d'items j'interroge les tables suivantes:

PROFILES
Id
LastName
FirstName

PROJETS
Id
Name
ProfileIdFK

ITEMS
Id
Name
ProjetIdFK

Et de aime à aimé je dois interroger les tables suivantes

NEWS
Id
NewsTypeIdFK
ItemsIdFK
ProfileIdFK
ProfileFollowerIdFK

NEWSTYPE
Id
Name

Dans la table NewsType, j'ai les données suivantes

Id----------Name
1-----------s'est abonné
2-----------à aimé
3-----------à commenté

Et la table news permet de savoir qui fait quoi

Pour exemple:
Id---NewsTypeIdFK---ItemsIdFK---ProfileIdFK---ProfileFollowerIdFK
1----2------------------14------------62-----------NULL
2----1------------------NULL----------65----------62

A la 1ere ligne on sait que le profile 62 aime l'item 14
A la 2eme ligne on sait que le profile 62 suit le profile 65

J'aimerai donc lister les données suivantes
LastName---FirstName---Nbre Projet---Nbre Items---Nbre Aime---Nbre Aimé

Nbre Abonné---Nbre Abonnement

Je sais pas ou je suis parti mais pour le moment j'arrive à avoir le bon résultat.

C'est pas un peu n'importe quoi ?----->

Code:

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
36
37
38
 
select 
	p.ProfileId,
	p.LastName, 
	(select 
		(count(n.ProfileIdFK)/(
			select COUNT(p2.ProfileId) 
				from Profiles as p2))
	from 
		Profiles as p1, News n 
	where 
		n.ProfileIdFK = p.ProfileId 
	and 
		n.NewsTypeIdFK = 4) as 'aime',
 
	(select 
		(count( n1.ItemsIdFK)/(
			select count(p4.ProfileId) 
				from Profiles as p4)) 
	from 
		Profiles as p3, 
		News as n1, 
		Projects as pj, 
		Items as it 
	where 
		it.ProjectIdFK = pj.ProjectId 
	and 
		pj.ProfileIdFK = p.ProfileId
	and 
		n1.NewsTypeIdFK = 4 
	and 
		n1.ItemsIdFK = it.ItemsId) as 'aimé'
 
from 
	Profiles as p
group by 
	p.ProfileId, 
	p.LastName

Ce que je ne comprend pas, c'est qu'il faut que je divise par le nombre total de profile pour avoir le bon résultat.

25/11/2010, 11h32
dublow

J'ai finalement réussi la requete.