Prev: SSRS EXCEL Datasource
Next: Help with sort on Recursive CTE
From: Jennifer Mathews on 12 Feb 2010 07:30
I have been trying to redo some of my SQL without temp tables
and Loops so here is another question I have not been able to figure out.

Table Bk_Reviews
Person_ID int
Book_ID int
Book_Comment varchar(2000)

Person_ID Book_ID Book_Comment
100 50 whatever1
100 60 whatever2
105 70 whatever3
105 80 whatever4 << same book
105 80 whatever5 << same book

To get the total number of comments by a person I use:

DECLARE @cnt int
SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105
RETURNS 3 for Person_ID=105

Most people only comment once for a book but people can have
multiple comments per book such as Person_ID=105 \ Book_ID=80

I am currently using a temp table to count the number of
Total UNIQUE comments excluding duplicates per book.

SELECT DISTINCT Book_ID
INTO #tmp_Person2Book
FROM Bk_Reviews WHERE Person_ID = 105

SELECT @cnt = COUNT(*) FROM #tmp_Person2Book
RETURNS 2 for Person_ID=105

Person_ID Book_ID
105 70
105 80 << doesn't matter that has two comments

Is there anyway I can do this in a single SELECT statement
instead of using the temp table tmp_Person2Book?

Thanks

From: Hugo Kornelis on 12 Feb 2010 07:47
On Fri, 12 Feb 2010 07:30:32 -0500, Jennifer Mathews wrote:

>I have been trying to redo some of my SQL without temp tables
>and Loops so here is another question I have not been able to figure out.
(snip)
>Is there anyway I can do this in a single SELECT statement
>instead of using the temp table tmp_Person2Book?


Hi Jennifer,

SELECT COUNT(DISTINCT Book_ID)
FROM Bk_Reviews
WHERE Person_ID = 105;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: milos.radivojevic on 12 Feb 2010 08:20
SELECT Person_ID, COUNT(DISTINCT Book_ID) Cou
FROM Bk_Reviews
GROUP BY Person_ID

or, in your code sample:

DECLARE @cnt int
SELECT @cnt = COUNT(DISTINCT Book_ID) FROM Bk_Reviews WHERE Person_ID = 105
SELECT @cnt

From: jgurgul on 12 Feb 2010 08:37
Hi Jennifer

DECLARE @Bk_Reviews TABLE(Person_ID int,Book_ID int,Book_Comment
varchar(2000))
INSERT INTO @Bk_Reviews VALUES(100,50,'whatever1')
INSERT INTO @Bk_Reviews VALUES(100,60,'whatever2')
INSERT INTO @Bk_Reviews VALUES(105,70,'whatever3')
INSERT INTO @Bk_Reviews VALUES(105,80,'whatever4')
INSERT INTO @Bk_Reviews VALUES(105,80,'whatever5')

SELECT Person_ID,COUNT(*) [TotalComments]
FROM @Bk_Reviews
GROUP BY Person_ID

SELECT Person_ID,COUNT(DISTINCT Book_ID) [UniqueComments]
FROM @Bk_Reviews
GROUP BY Person_ID

Jon

"Jennifer Mathews" wrote:

> I have been trying to redo some of my SQL without temp tables
> and Loops so here is another question I have not been able to figure out.
>
> Table Bk_Reviews
> Person_ID int
> Book_ID int
> Book_Comment varchar(2000)
>
> Person_ID Book_ID Book_Comment
> 100 50 whatever1
> 100 60 whatever2
> 105 70 whatever3
> 105 80 whatever4 << same book
> 105 80 whatever5 << same book
>
> To get the total number of comments by a person I use:
>
> DECLARE @cnt int
> SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105
> RETURNS 3 for Person_ID=105
>
> Most people only comment once for a book but people can have
> multiple comments per book such as Person_ID=105 \ Book_ID=80
>
> I am currently using a temp table to count the number of
> Total UNIQUE comments excluding duplicates per book.
>
> SELECT DISTINCT Book_ID
> INTO #tmp_Person2Book
> FROM Bk_Reviews WHERE Person_ID = 105
>
> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book
> RETURNS 2 for Person_ID=105
>
> Person_ID Book_ID
> 105 70
> 105 80 << doesn't matter that has two comments
>
> Is there anyway I can do this in a single SELECT statement
> instead of using the temp table tmp_Person2Book?
>
> Thanks
>
> .
>
From: Jennifer Mathews on 12 Feb 2010 16:27
THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had known
this awhile ago instead of using temp tables.

Thanks again.


"Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>I have been trying to redo some of my SQL without temp tables and Loops so here is
>another question I have not been able to figure out.
>
> Table Bk_Reviews
> Person_ID int
> Book_ID int Book_Comment varchar(2000)
> Person_ID Book_ID Book_Comment
> 100 50 whatever1
> 100 60 whatever2
> 105 70 whatever3
> 105 80 whatever4 << same book
> 105 80 whatever5 << same book
>
> To get the total number of comments by a person I use:
>
> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105
> RETURNS 3 for Person_ID=105
>
> Most people only comment once for a book but people can have multiple comments per
> book such as Person_ID=105 \ Book_ID=80
>
> I am currently using a temp table to count the number of Total UNIQUE comments
> excluding duplicates per book.
>
> SELECT DISTINCT Book_ID INTO #tmp_Person2Book
> FROM Bk_Reviews WHERE Person_ID = 105
> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105
>
> Person_ID Book_ID 105 70 105 80 << doesn't matter that has
> two comments
>
> Is there anyway I can do this in a single SELECT statement instead of using the temp
> table tmp_Person2Book?
> Thanks
>

 |  Next  |  Last
Pages: 1 2 3
Prev: SSRS EXCEL Datasource
Next: Help with sort on Recursive CTE