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.
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 descThis 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:commenttablelocid: 34comment: nice photos!locid: 44comment:I like the colours of the houseSITESauthor: 33name: markauthor: 34name: Henryif a.locid = 34 then b.author = HenryBasically 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 descBut I need something likeselect 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 descI 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 2select top 15 a.locid,b.author from commenttable a inner join sites b on a.locid = b.id where author = 'Henry' order by stamp descneeds a date comparison, no count() needed. |
|
|
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 |
|
|
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 6Incorrect 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 descThank you.Ahhh... to make it even faster and not query every row:DECLARE @dback DATETIMESET @dback = GETDATE() - 7select 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) |
|
|
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 |
|
|
|
|
|
|
|