Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Perform Count(*) with inner join

Author  Topic 

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-04-26 : 20:37:59
Hi there.

My query:
select top 15 a.locid,b.author from commenttable a inner join sites b on a.locid = b.id where author = 'Henry' order by stamp desc

This selects top 15 records from newest to oldest ('stamp' is time/date stamp) which are comments taken from 'commenttable'.

The comment table only has a numeric value for the identity of what is being commented on, thus I use a JOIN to match the identity# (locid) to b.id where the 'sites' table contains usernames.

So for example if I have:
commenttable
locid: 34
comment: nice photos!

locid: 44
comment:I like the colours of the house

SITES
author: 33
name: mark

author: 34
name: Henry

if a.locid = 34 then b.author = Henry

Basically I just want to return a count for how many rows contains comments meant for Henry (or any user) based on changing the value of 'author'

This works:
select top 15 a.locid,b.author from commenttable a inner join sites b on a.locid = b.id where author = 'Henry' order by stamp desc

But I need something like
select count(*) as total select top 15 a.locid,b.author from commenttable a inner join sites b on a.locid = b.id where author = 'Henry' order by stamp desc

I know that's not a valid command, I just need a count based on the sql command shown. :)

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-04-26 : 20:45:20
Actually to further complicate things, I'd like to show a count of the last xx days of comments left for someone, then display the comments from the last xx days.

So query 1:
select count(*) top 15 a.locid,b.author from commenttable a inner join sites b on a.locid = b.id where author = 'Henry' order by stamp desc

*show me the number of new comments left for me in last 10 days, not actual results just the number of comments*

The syntax is incorrect of course and it lacks the date comparison.

query 2
select top 15 a.locid,b.author from commenttable a inner join sites b on a.locid = b.id where author = 'Henry' order by stamp desc

needs a date comparison, no count() needed.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-27 : 09:46:38
Something like this might work for you:

QUERY 1:
[CODE]
select top 15 a.locid,b.author,
(SELECT COUNT(*) from commenttable a inner join sites b on a.locid = b.id
where author = 'Henry') AS TotalCount
from commenttable a inner join sites b on a.locid = b.id
where author = 'Henry' order by stamp desc


[/CODE]

QUERY 2: Replace the red text with the appropriate column name and date range
[CODE]

select top 15 a.locid,b.author,
(SELECT COUNT(*) from commenttable a inner join sites b on a.locid = b.id
where author = 'Henry' and
b.commentdate BETWEEN '20120112' and '20120412' AS TotalCount from
commenttable a inner join sites b on a.locid = b.id where author = 'Henry' and
b.commentdate BETWEEN '20120112' and '20120412' order by stamp desc

[/CODE]
In the future if you post your DDLs and some data as described at this site,
it helps us to help you better :
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-04-27 : 15:41:50
The first example worked but it returned 603 as the totalcount which is all comments because there was no date specified. The second example resulted in : Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'. I believe it was due to a ) bracket.

This was the final result:

select a.locid,b.creator,
(SELECT COUNT(*) from commenttable a inner join sites b on a.locid = b.id
where author = 'Henry' and a.stamp > getdate()-4) AS TotalCount
from commenttable a inner join sites b on a.locid = b.id
where author = 'Henry' and a.stamp > getdate()-4 order by stamp desc


Thank you.

Ahhh... to make it even faster and not query every row:

DECLARE @dback DATETIME
SET @dback = GETDATE() - 7

select a.locid,b.creator,
(SELECT COUNT(*) from commenttable a inner join sites b on a.locid = b.id
where author = 'Henry' and a.stamp > @dback)
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-27 : 20:06:22
Glad that you fixed the syntax error, and made your query faster...
Now you can change your name to sqlmaster
Go to Top of Page
   

- Advertisement -