SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Perform Count(*) with inner join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Starting Member

Canada
40 Posts

Posted - 04/26/2013 :  20:37:59  Show Profile  Reply with Quote
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. :)

Edited by - sqlconfused on 04/26/2013 20:38:32

sqlconfused
Starting Member

Canada
40 Posts

Posted - 04/26/2013 :  20:45:20  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/27/2013 :  09:46:38  Show Profile  Reply with Quote
Something like this might work for you:

QUERY 1:

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




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


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


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
Starting Member

Canada
40 Posts

Posted - 04/27/2013 :  15:41:50  Show Profile  Reply with Quote
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)

Edited by - sqlconfused on 04/27/2013 16:13:42
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/27/2013 :  20:06:22  Show Profile  Reply with Quote
Glad that you fixed the syntax error, and made your query faster...
Now you can change your name to sqlmaster

Edited by - MuMu88 on 04/27/2013 20:13:41
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000