June 15, 2011 at 5:13 am
I have following sample script to produce/understand the problem:
CREATE TABLE [dbo].[tblVideo](
[VideoId] [int] IDENTITY(1,1) NOT NULL,
[VideoTitle] [varchar](50) NULL,
[VideoCategoryId] [int] NOT NULL,
CONSTRAINT [PK_tblVideo] PRIMARY KEY CLUSTERED
(
[VideoId] 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 rows
INSERT INTO [tblVideo] ([VideoTitle] ,[VideoCategoryId])
select 'Video1', 1037
union
select 'Video2', 1037
GO
CREATE TABLE [dbo].[tblTracking](
[ViewerTrackingId] [int] IDENTITY(1,1) NOT NULL,
[VideoId] [int] NOT NULL,
[VideoCategoryId] [int] NOT NULL,
CONSTRAINT [PK_tblTracking] PRIMARY KEY CLUSTERED
(
[ViewerTrackingId] 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 rows
INSERT INTO [tblTracking] ([VideoId], [VideoCategoryId])
select 1, 1037
GO
CREATE TABLE [dbo].[tblFavorite](
[FavoriteId] [int] IDENTITY(1,1) NOT NULL,
[VideoId] [int] NOT NULL,
[VideoCateoryId] [int] NOT NULL,
[ViewerId] [int] NOT NULL,
CONSTRAINT [PK_tblFavorite] PRIMARY KEY CLUSTERED
(
[FavoriteId] 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 rows
INSERT INTO [tblFavorite] ([VideoId], [VideoCateoryId], [ViewerId])
select 1, 1037, 1
Union all
select 1, 1037, 1
Union all
select 1, 1037, 1
GO
CREATE TABLE [dbo].[Rating](
[RatingId] [int] IDENTITY(1,1) NOT NULL,
[VideoId] [int] NOT NULL,
[VideoCategoryId] [int] NOT NULL,
CONSTRAINT [PK_Rating] PRIMARY KEY CLUSTERED
(
[RatingId] 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 [Rating] ([VideoId], [VideoCategoryId])
select 1, 1037
union all
select 1, 1037
GO
--- I want to produce results for given video title as
select VideoTitle,
COUNT(favoriteId) as TotalFavorite,
COUNT(ViewerTrackingId) as TotalViewed,
COUNT(RatingId) as TotalRating
from tblVideo v
left join tblFavorite f on v.VideoId = f.VideoId
left join tblTracking t on v.videoId = t.videoId
left join Rating r on v.VideoId = r.VideoId
where v.VideoCategoryId = 1037
group by VideoTitle
-- which is giving me wrong count from each table
-- the result should be
VideoTitleTotalFavoriteTotalViewedTotalRating
Video1 3 1 2
Please help.
Shamshad Ali.
June 15, 2011 at 5:34 am
To solve the problem i may use following:
select VideoTitle,
COUNT(distinct favoriteId) as TotalFavorite,
COUNT(distinct ViewerTrackingId) as TotalViewed,
COUNT(distinct RatingId) as TotalRating
from tblVideo v
left join tblFavorite f on v.VideoId = f.VideoId
left join tblTracking t on v.videoId = t.videoId
left join Rating r on v.VideoId = r.VideoId
where v.VideoCategoryId = 1037
group by VideoTitle
also mentioned in following:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57053
subquery may kill the performance and NOT recommended, How about DISTINCT ?
I have billions of records in joining tables (these are transaction tables) - would it be OK or may kill performance. I am using this SP on a home page which might hit server performance. What would be the ultimate solution?
Shamshad Ali.
June 15, 2011 at 6:14 am
This was removed by the editor as SPAM
June 15, 2011 at 7:20 am
The result is same when using COUNT(expression) and COUNT(ALL expression)
try following and see the difference - COUNT(All expression) is giving incorrect results.
-- COUNT(DISTINCT expression)
select VideoTitle,
COUNT(distinct favoriteId) as TotalFavorite,
COUNT(distinct ViewerTrackingId) as TotalViewed,
COUNT(distinct RatingId) as TotalRating
from tblVideo v
inner join tblFavorite f on v.VideoId = f.VideoId
inner join tblTracking t on v.videoId = t.videoId
inner join Rating r on v.VideoId = r.VideoId
where v.VideoCategoryId = 1037
group by VideoTitle
-- COUNT(ALL expression)
select VideoTitle,
COUNT(ALL favoriteId) as TotalFavorite,
COUNT(ALL ViewerTrackingId) as TotalViewed,
COUNT(ALL RatingId) as TotalRating
from tblVideo v
inner join tblFavorite f on v.VideoId = f.VideoId
inner join tblTracking t on v.videoId = t.videoId
inner join Rating r on v.VideoId = r.VideoId
where v.VideoCategoryId = 1037
group by VideoTitle
June 15, 2011 at 7:38 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply