Prev: Query help
Next: How much performance impact due to database mirrorring
From: Nightcrawler on 17 Jul 2007 15:23
I have the following tables and query

CREATE TABLE [dbo].[PlayList]
(
[PlayListId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Comment] [varchar](1000)
)

CREATE TABLE [dbo].[PlayListRating](
[PlayListId] [uniqueidentifier] NOT NULL,
[RatedBy] [uniqueidentifier] NOT NULL,
[Rating] [float] NOT NULL,
[DateRated] [datetime] NOT NULL DEFAULT (getdate())
)

CREATE TABLE [dbo].[Track](
[TrackId] [int] IDENTITY(1,1) NOT NULL,
[PlayListId] [uniqueidentifier] NOT NULL,
[Title] [varchar](128)
)

I have the following Query

SELECT PlayList.PlayListId, PlayList.UserId, COUNT(Track.TrackId)
AS TrackCount, COUNT(PlayListRating.Rating) AS RatingCount,
AVG(PlayListRating.Rating)
AS RatingAverage, PlayList.Comment
FROM PlayList LEFT OUTER JOIN
Track ON PlayList.PlayListId = Track.PlayListId
LEFT OUTER JOIN
PlayListRating ON PlayList.PlayListId =
PlayListRating.PlayListId
GROUP BY PlayList.PlayListId, PlayList.UserId, PlayList.DatePlayed,
PlayList.Comment
ORDER BY PlayList.DatePlayed DESC

The TrackCount and Rating Averge come out ok, but the Rating Count is
totally off, it comes out to be the same count as the TrackCount. I am
assuming it is because I am joining more tables than one.

Is there a way to solve this problem and have the query return the
information I want?

Thanks

From: Roy Harvey on 17 Jul 2007 15:50
I think this comes close. Since no test data was provided it is
untested, and could easily have problems.

Since the column PlayList.DatePlayed referenced in the sample query
was not included in the sample tables, it is not present in my query.

The basic idea is to summarize both the "many" tables by PlayListId
before joining, with derived tables making that possible.

SELECT PlayList.PlayListId, PlayList.UserId,
SUM(A.TrackCount) AS TrackCount,
SUM(B.RatingCount) AS RatingCount,
SUM(B.RatingTotal) /
SUM(B.RatingCount) AS RatingAverage,
PlayList.Comment
FROM PlayList
LEFT OUTER
JOIN (SELECT Track.PlayListId,
count(Track.TrackId) as TrackCount
FROM Track
GROUP BY Track.PlayListId) as A
ON PlayList.PlayListId = A.PlayListId
LEFT OUTER
JOIN (SELECT PlayListRating.PlayListId,
count(PlayListRating.Rating) as RatingCount,
sum(PlayListRating.Rating) as RatingTotal
FROM PlayListRating
GROUP BY PlayListRating.PlayListId) as B
ON PlayList.PlayListId = B.PlayListId
GROUP BY PlayList.PlayListId, PlayList.UserId,
PlayList.Comment

Roy Harvey
Beacon Falls, CT

On Tue, 17 Jul 2007 19:23:12 -0000, Nightcrawler
<thomas.zaleski(a)gmail.com> wrote:

>I have the following tables and query
>
>CREATE TABLE [dbo].[PlayList]
>(
> [PlayListId] [uniqueidentifier] NOT NULL,
> [UserId] [uniqueidentifier] NOT NULL,
> [Comment] [varchar](1000)
>)
>
>CREATE TABLE [dbo].[PlayListRating](
> [PlayListId] [uniqueidentifier] NOT NULL,
> [RatedBy] [uniqueidentifier] NOT NULL,
> [Rating] [float] NOT NULL,
> [DateRated] [datetime] NOT NULL DEFAULT (getdate())
>)
>
>CREATE TABLE [dbo].[Track](
> [TrackId] [int] IDENTITY(1,1) NOT NULL,
> [PlayListId] [uniqueidentifier] NOT NULL,
> [Title] [varchar](128)
>)
>
>I have the following Query
>
>SELECT PlayList.PlayListId, PlayList.UserId, COUNT(Track.TrackId)
>AS TrackCount, COUNT(PlayListRating.Rating) AS RatingCount,
>AVG(PlayListRating.Rating)
> AS RatingAverage, PlayList.Comment
>FROM PlayList LEFT OUTER JOIN
> Track ON PlayList.PlayListId = Track.PlayListId
>LEFT OUTER JOIN
> PlayListRating ON PlayList.PlayListId =
>PlayListRating.PlayListId
>GROUP BY PlayList.PlayListId, PlayList.UserId, PlayList.DatePlayed,
>PlayList.Comment
>ORDER BY PlayList.DatePlayed DESC
>
>The TrackCount and Rating Averge come out ok, but the Rating Count is
>totally off, it comes out to be the same count as the TrackCount. I am
>assuming it is because I am joining more tables than one.
>
>Is there a way to solve this problem and have the query return the
>information I want?
>
>Thanks
From: Tom Moreau on 17 Jul 2007 15:53
Do you have some sample data and expected results? Also, your tables have
no PK's.

That said, consider changing:

COUNT(PlayListRating.Rating)

to

COUNT(DISTINCT PlayListRating.RatedBy)

This assumes that a playlist can be rated only once by a particular person.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Nightcrawler" <thomas.zaleski(a)gmail.com> wrote in message
news:1184700192.032090.177570(a)g12g2000prg.googlegroups.com...
I have the following tables and query

CREATE TABLE [dbo].[PlayList]
(
[PlayListId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Comment] [varchar](1000)
)

CREATE TABLE [dbo].[PlayListRating](
[PlayListId] [uniqueidentifier] NOT NULL,
[RatedBy] [uniqueidentifier] NOT NULL,
[Rating] [float] NOT NULL,
[DateRated] [datetime] NOT NULL DEFAULT (getdate())
)

CREATE TABLE [dbo].[Track](
[TrackId] [int] IDENTITY(1,1) NOT NULL,
[PlayListId] [uniqueidentifier] NOT NULL,
[Title] [varchar](128)
)

I have the following Query

SELECT PlayList.PlayListId, PlayList.UserId, COUNT(Track.TrackId)
AS TrackCount, COUNT(PlayListRating.Rating) AS RatingCount,
AVG(PlayListRating.Rating)
AS RatingAverage, PlayList.Comment
FROM PlayList LEFT OUTER JOIN
Track ON PlayList.PlayListId = Track.PlayListId
LEFT OUTER JOIN
PlayListRating ON PlayList.PlayListId =
PlayListRating.PlayListId
GROUP BY PlayList.PlayListId, PlayList.UserId, PlayList.DatePlayed,
PlayList.Comment
ORDER BY PlayList.DatePlayed DESC

The TrackCount and Rating Averge come out ok, but the Rating Count is
totally off, it comes out to be the same count as the TrackCount. I am
assuming it is because I am joining more tables than one.

Is there a way to solve this problem and have the query return the
information I want?

Thanks

From: --CELKO-- on 18 Jul 2007 20:47
>> Is there a way to solve this problem and have the query return theinformation I want? <<

Use a relational database design instead of this monster. Does the
RIAA know that you are using magical "uniqueidentifier" instead of
indusry standards? I am gettign ready to go home, so i will catchyou
with another posting.

From: xyb on 18 Jul 2007 22:15
On 7 18 , 3 23 , Nightcrawler <thomas.zale...(a)gmail.com> wrote:
> I have the following tables and query
>
> CREATE TABLE [dbo].[PlayList]
> (
> [PlayListId] [uniqueidentifier] NOT NULL,
> [UserId] [uniqueidentifier] NOT NULL,
> [Comment] [varchar](1000)
> )
>
> CREATE TABLE [dbo].[PlayListRating](
> [PlayListId] [uniqueidentifier] NOT NULL,
> [RatedBy] [uniqueidentifier] NOT NULL,
> [Rating] [float] NOT NULL,
> [DateRated] [datetime] NOT NULL DEFAULT (getdate())
> )
>
> CREATE TABLE [dbo].[Track](
> [TrackId] [int] IDENTITY(1,1) NOT NULL,
> [PlayListId] [uniqueidentifier] NOT NULL,
> [Title] [varchar](128)
> )
>
> I have the following Query
>
> SELECT PlayList.PlayListId, PlayList.UserId, COUNT(Track.TrackId)
> AS TrackCount, COUNT(PlayListRating.Rating) AS RatingCount,
> AVG(PlayListRating.Rating)
> AS RatingAverage, PlayList.Comment
> FROM PlayList LEFT OUTER JOIN
> Track ON PlayList.PlayListId = Track.PlayListId
> LEFT OUTER JOIN
> PlayListRating ON PlayList.PlayListId =
> PlayListRating.PlayListId
> GROUP BY PlayList.PlayListId, PlayList.UserId, PlayList.DatePlayed,
> PlayList.Comment
> ORDER BY PlayList.DatePlayed DESC
>
> The TrackCount and Rating Averge come out ok, but the Rating Count is
> totally off, it comes out to be the same count as the TrackCount. I am
> assuming it is because I am joining more tables than one.
>
> Is there a way to solve this problem and have the query return the
> information I want?
>
> Thanks
I guess you need use sum function :)
SELECT PlayList.PlayListId, PlayList.UserId, COUNT(Track.TrackId)
AS TrackCount, SUM(PlayListRating.Rating) AS RatingCount,
AVG(PlayListRating.Rating)
AS RatingAverage, PlayList.Comment
FROM PlayList LEFT OUTER JOIN
Track ON PlayList.PlayListId = Track.PlayListId
LEFT OUTER JOIN
PlayListRating ON PlayList.PlayListId =
PlayListRating.PlayListId
GROUP BY PlayList.PlayListId, PlayList.UserId, PlayList.DatePlayed,
PlayList.Comment
ORDER BY PlayList.DatePlayed DESC

 |  Next  |  Last
Pages: 1 2
Prev: Query help
Next: How much performance impact due to database mirrorring