Prev: stored procedure syntax?
Next: How to tell when a transaction has been rolled back?
From: Luciano (DOC) on 13 Mar 2010 02:23
I have a table where each record is a newspaper article, having this
structure:

idArticle
dateArticle
idNewspaper (relationed with other table)
idArgument (relationed with other table)
title
timesRead (times the article has been read).

I want to select for each article title, the sum of timesRead for all the
articles of the relative idNewspaper and the sum of timesRead for all the
articles of the relative idArgument.
For example, if The New York Times has 3 article in the table, for a total
of
20 readings, and 2 argumentes (comparted with other newspapers, the sum
must include them) I want to get:
article1, 20, 57
article2, 20, 57
article3, 20, 43

I can do it easily using PARTITION (as I learnt here:-), but what I need to
do is summing timesRead only in a specified range of date, which I think is
not
possibile using PARTITION.
If I were using Access, I would save two queries with the sum of readings
for idNewspaper and idArgment and then I would join it with the main select.
I don't know how to do with sql server 2005.
Thanks.

Luciano



From: Paul Shapiro on 13 Mar 2010 13:06

"Luciano (DOC)" <lucianodoc(a)luciano.doc> wrote in message
news:4b9b3dc7$0$1108$4fafbaef(a)reader2.news.tin.it...
> I have a table where each record is a newspaper article, having this
> structure:
>
> idArticle
> dateArticle
> idNewspaper (relationed with other table)
> idArgument (relationed with other table)
> title
> timesRead (times the article has been read).
>
> I want to select for each article title, the sum of timesRead for all the
> articles of the relative idNewspaper and the sum of timesRead for all the
> articles of the relative idArgument.
> For example, if The New York Times has 3 article in the table, for a
> total
> of
> 20 readings, and 2 argumentes (comparted with other newspapers, the sum
> must include them) I want to get:
> article1, 20, 57
> article2, 20, 57
> article3, 20, 43
>
> I can do it easily using PARTITION (as I learnt here:-), but what I need
> to
> do is summing timesRead only in a specified range of date, which I think
> is not
> possibile using PARTITION.
> If I were using Access, I would save two queries with the sum of readings
> for idNewspaper and idArgment and then I would join it with the main
> select.
> I don't know how to do with sql server 2005.

I don't understand the aggregations you're trying to perform, but it maybe
you want Group By, not Partition? If you want the sum of times read by
article and newspaper, for example, it would be something like:
Select articleID, newspaperID, sum(timesRead) as TotalTimesRead
From NewspaperArticles
Group By articleID, newspaperID

If that's not what you're looking for, then please post back with the table
schema and a clearer description.

 | 
Pages: 1
Prev: stored procedure syntax?
Next: How to tell when a transaction has been rolled back?