count records in a top 10 query

Hi

Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...

i have this so far, and it dosent give me anything...

col1 is varchar 254

SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)

ex of output

place1 100
place2 50
place3 25
...
place11 500

a query that only gives me the place11 number is enough

thx in advance //MrHere is the number of records that are not in the top 10 list

select count(*) number
from myTable
where col1 not in
(select top 10 col1 from myTable)
group by col1
order by count(*) desc
)

Count Records in a Group

Hello All.
I'm running SSRS 2005 on top of a sql2000 database.
I need to create a report that counts the number of invoices salesreps
process per month.
The fields I have selected are;
SalesRep ID Invoice No Invoice Date
1 5467 2/22/2006 3:06:17 PM
5 4526 2/22/2006 3:29:56 PM
8 6589 6/14/2005 4:20:26 PM
5 8569 2/22/2006 3:29:56 PM
5 2563 6/10/2007 8:29:56 AM
5 1523 2/22/2006 3:29:56 PM
8 9876 8/23/2006 5:29:56 PM
1 7563 4/23/2006 1:29:56 PM
What i want to do is group by Salesrep ID, then show the total number of
invoices that rep did per month;
SalesRep ID Month TotalInvoices Processed
1 Jan 4
Feb 2
Mar 5
5 Jan 5
Feb 6
Mar 3
8 Jan 2
Feb 10
Mar 20 ... and so on.
I'm not sure exactly how to do this. Also how do I convert the date format
into just showing the month, not every minute of every day?
Any help is much appreciated.
Thanks.You want to do this with the sql statement. Since you didn't show your SQL I
had to make up names.
select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
invoices_count from yourtable a
group by a.salesrepid, datepart(month, a.invoicedate)
order by a.salesrepid, month
Note this gives you month by number which is what you need in order to order
it properly, if you want by month name then add that in and still order by
the month number to keep in in the proper order.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> Hello All.
> I'm running SSRS 2005 on top of a sql2000 database.
> I need to create a report that counts the number of invoices salesreps
> process per month.
> The fields I have selected are;
> SalesRep ID Invoice No Invoice Date
>
> 1 5467 2/22/2006 3:06:17 PM
> 5 4526 2/22/2006 3:29:56 PM
> 8 6589 6/14/2005 4:20:26 PM
> 5 8569 2/22/2006 3:29:56 PM
> 5 2563 6/10/2007 8:29:56 AM
> 5 1523 2/22/2006 3:29:56 PM
> 8 9876 8/23/2006 5:29:56 PM
> 1 7563 4/23/2006 1:29:56 PM
> What i want to do is group by Salesrep ID, then show the total number of
> invoices that rep did per month;
> SalesRep ID Month TotalInvoices Processed
> 1 Jan 4
> Feb 2
> Mar 5
> 5 Jan 5
> Feb 6
> Mar 3
> 8 Jan 2
> Feb 10
> Mar 20 ... and so on.
>
> I'm not sure exactly how to do this. Also how do I convert the date format
> into just showing the month, not every minute of every day?
> Any help is much appreciated.
> Thanks.
>|||Thank you soo much Bruce.
Here is the statement;
SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
dbo.invoice_hdr.order_date
FROM dbo.contacts INNER JOIN
dbo.invoice_hdr ON dbo.contacts.id =dbo.invoice_hdr.salesrep_id
"Bruce L-C [MVP]" wrote:
> You want to do this with the sql statement. Since you didn't show your SQL I
> had to make up names.
> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> invoices_count from yourtable a
> group by a.salesrepid, datepart(month, a.invoicedate)
> order by a.salesrepid, month
> Note this gives you month by number which is what you need in order to order
> it properly, if you want by month name then add that in and still order by
> the month number to keep in in the proper order.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > Hello All.
> >
> > I'm running SSRS 2005 on top of a sql2000 database.
> >
> > I need to create a report that counts the number of invoices salesreps
> > process per month.
> >
> > The fields I have selected are;
> >
> > SalesRep ID Invoice No Invoice Date
> >
> >
> > 1 5467 2/22/2006 3:06:17 PM
> > 5 4526 2/22/2006 3:29:56 PM
> > 8 6589 6/14/2005 4:20:26 PM
> > 5 8569 2/22/2006 3:29:56 PM
> > 5 2563 6/10/2007 8:29:56 AM
> > 5 1523 2/22/2006 3:29:56 PM
> > 8 9876 8/23/2006 5:29:56 PM
> > 1 7563 4/23/2006 1:29:56 PM
> >
> > What i want to do is group by Salesrep ID, then show the total number of
> > invoices that rep did per month;
> >
> > SalesRep ID Month TotalInvoices Processed
> > 1 Jan 4
> > Feb 2
> > Mar 5
> >
> > 5 Jan 5
> > Feb 6
> > Mar 3
> >
> > 8 Jan 2
> > Feb 10
> > Mar 20 ... and so on.
> >
> >
> > I'm not sure exactly how to do this. Also how do I convert the date format
> > into just showing the month, not every minute of every day?
> >
> > Any help is much appreciated.
> > Thanks.
> >
>
>|||Bruce that worked perfectly!!
Thanks again!
"Damon Johnson" wrote:
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
> > You want to do this with the sql statement. Since you didn't show your SQL I
> > had to make up names.
> >
> > select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> > invoices_count from yourtable a
> > group by a.salesrepid, datepart(month, a.invoicedate)
> > order by a.salesrepid, month
> >
> > Note this gives you month by number which is what you need in order to order
> > it properly, if you want by month name then add that in and still order by
> > the month number to keep in in the proper order.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> > news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > > Hello All.
> > >
> > > I'm running SSRS 2005 on top of a sql2000 database.
> > >
> > > I need to create a report that counts the number of invoices salesreps
> > > process per month.
> > >
> > > The fields I have selected are;
> > >
> > > SalesRep ID Invoice No Invoice Date
> > >
> > >
> > > 1 5467 2/22/2006 3:06:17 PM
> > > 5 4526 2/22/2006 3:29:56 PM
> > > 8 6589 6/14/2005 4:20:26 PM
> > > 5 8569 2/22/2006 3:29:56 PM
> > > 5 2563 6/10/2007 8:29:56 AM
> > > 5 1523 2/22/2006 3:29:56 PM
> > > 8 9876 8/23/2006 5:29:56 PM
> > > 1 7563 4/23/2006 1:29:56 PM
> > >
> > > What i want to do is group by Salesrep ID, then show the total number of
> > > invoices that rep did per month;
> > >
> > > SalesRep ID Month TotalInvoices Processed
> > > 1 Jan 4
> > > Feb 2
> > > Mar 5
> > >
> > > 5 Jan 5
> > > Feb 6
> > > Mar 3
> > >
> > > 8 Jan 2
> > > Feb 10
> > > Mar 20 ... and so on.
> > >
> > >
> > > I'm not sure exactly how to do this. Also how do I convert the date format
> > > into just showing the month, not every minute of every day?
> > >
> > > Any help is much appreciated.
> > > Thanks.
> > >
> >
> >
> >|||First, you do not need TOP 100 percent. That means you want all the records
which is what you get without the TOP syntax.
SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
datename(month,b.order_date) as Month_Name,count(*) as invoices_count
FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id =b.salesrep_id
where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
group by a.salesrep_id, datepart(month,b.order_date)
order by a.salesrep_id, month
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
>> You want to do this with the sql statement. Since you didn't show your
>> SQL I
>> had to make up names.
>> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
>> invoices_count from yourtable a
>> group by a.salesrepid, datepart(month, a.invoicedate)
>> order by a.salesrepid, month
>> Note this gives you month by number which is what you need in order to
>> order
>> it properly, if you want by month name then add that in and still order
>> by
>> the month number to keep in in the proper order.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
>> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
>> > Hello All.
>> >
>> > I'm running SSRS 2005 on top of a sql2000 database.
>> >
>> > I need to create a report that counts the number of invoices salesreps
>> > process per month.
>> >
>> > The fields I have selected are;
>> >
>> > SalesRep ID Invoice No Invoice Date
>> >
>> >
>> > 1 5467 2/22/2006 3:06:17 PM
>> > 5 4526 2/22/2006 3:29:56 PM
>> > 8 6589 6/14/2005 4:20:26 PM
>> > 5 8569 2/22/2006 3:29:56 PM
>> > 5 2563 6/10/2007 8:29:56 AM
>> > 5 1523 2/22/2006 3:29:56 PM
>> > 8 9876 8/23/2006 5:29:56 PM
>> > 1 7563 4/23/2006 1:29:56 PM
>> >
>> > What i want to do is group by Salesrep ID, then show the total number
>> > of
>> > invoices that rep did per month;
>> >
>> > SalesRep ID Month TotalInvoices Processed
>> > 1 Jan 4
>> > Feb 2
>> > Mar 5
>> >
>> > 5 Jan 5
>> > Feb 6
>> > Mar 3
>> >
>> > 8 Jan 2
>> > Feb 10
>> > Mar 20 ... and so on.
>> >
>> >
>> > I'm not sure exactly how to do this. Also how do I convert the date
>> > format
>> > into just showing the month, not every minute of every day?
>> >
>> > Any help is much appreciated.
>> > Thanks.
>> >
>>|||OK Bruce I think this is my last request.
the DATENAME(month, invoice_date) returns the month only.
How do i get it to return the month and year. I will be setting this up as a
parameter query;
Between @.StartDate and @.EndDate
and want the user to put in May 07 and June 07
thanks again.
"Bruce L-C [MVP]" wrote:
> First, you do not need TOP 100 percent. That means you want all the records
> which is what you get without the TOP syntax.
> SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
> datename(month,b.order_date) as Month_Name,count(*) as invoices_count
> FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id => b.salesrep_id
> where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
> group by a.salesrep_id, datepart(month,b.order_date)
> order by a.salesrep_id, month
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> > Thank you soo much Bruce.
> > Here is the statement;
> >
> > SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> > dbo.invoice_hdr.order_date
> > FROM dbo.contacts INNER JOIN
> > dbo.invoice_hdr ON dbo.contacts.id => > dbo.invoice_hdr.salesrep_id
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You want to do this with the sql statement. Since you didn't show your
> >> SQL I
> >> had to make up names.
> >>
> >> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> >> invoices_count from yourtable a
> >> group by a.salesrepid, datepart(month, a.invoicedate)
> >> order by a.salesrepid, month
> >>
> >> Note this gives you month by number which is what you need in order to
> >> order
> >> it properly, if you want by month name then add that in and still order
> >> by
> >> the month number to keep in in the proper order.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> >> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> >> > Hello All.
> >> >
> >> > I'm running SSRS 2005 on top of a sql2000 database.
> >> >
> >> > I need to create a report that counts the number of invoices salesreps
> >> > process per month.
> >> >
> >> > The fields I have selected are;
> >> >
> >> > SalesRep ID Invoice No Invoice Date
> >> >
> >> >
> >> > 1 5467 2/22/2006 3:06:17 PM
> >> > 5 4526 2/22/2006 3:29:56 PM
> >> > 8 6589 6/14/2005 4:20:26 PM
> >> > 5 8569 2/22/2006 3:29:56 PM
> >> > 5 2563 6/10/2007 8:29:56 AM
> >> > 5 1523 2/22/2006 3:29:56 PM
> >> > 8 9876 8/23/2006 5:29:56 PM
> >> > 1 7563 4/23/2006 1:29:56 PM
> >> >
> >> > What i want to do is group by Salesrep ID, then show the total number
> >> > of
> >> > invoices that rep did per month;
> >> >
> >> > SalesRep ID Month TotalInvoices Processed
> >> > 1 Jan 4
> >> > Feb 2
> >> > Mar 5
> >> >
> >> > 5 Jan 5
> >> > Feb 6
> >> > Mar 3
> >> >
> >> > 8 Jan 2
> >> > Feb 10
> >> > Mar 20 ... and so on.
> >> >
> >> >
> >> > I'm not sure exactly how to do this. Also how do I convert the date
> >> > format
> >> > into just showing the month, not every minute of every day?
> >> >
> >> > Any help is much appreciated.
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>

Count Records Between two dates

Hi all,

I've got a quick question.

How would I count the number of records between two dates.

I started with something like this.

SELECT COUNT(*) AS COUNT, dtAdded
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')
GROUP BY dtAdded

but as you probably all know this ain't right. I would like to get just the number of records.

ThanksLeave off the GROUP BY.

-PatP|||got it...

SELECT COUNT(dtAdded) AS numRecords
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')

Thanks Patsql

count only records from left table ROLLUP or CUBE

Hello,

Using rollup I want to count the number of rows of a table
called Table1 which is LEFT JOINED with a table called Table2.

The problem is that we can have more than 1 rows in Table2 that
matched 1 row in Table1. As a consequence the count of rows in table1
is bigger than the real number of rows contained in table1.

For example:

Select COUNT(Table1.employeeID), Table1.country
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.phone_number = Table2.phone_number
GROUP BY Table1.country WITH ROLLUP

that works but give me a COUNT higher than the truth because I can have
several times the same phone number in Table2!

COUNT(DISTINCT...) would work if WITH ROLLUP not used BUT I want
absolutely to use ROLLUP.
Does someone know a workaround?

Help much appreciated.In your example, the workaround is very easy: remove the join with
Table2, because Table2 is not used at all in the query (there are no
conditions on this table, no columns selected from it; the only effect
of joining this table is the higher count, that you do not want).

Please post a query that is closer to your requirements, along with
DDL, sample data and expected results, as documented in:
http://www.aspfaq.com/etiquette.asp?id=5006

Razvansql

count of records greater than?

Hello All,

Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.

The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...

Code Snippet

=Count(Fields!ResponseTime.Value > "500")

However, this appears to just return the count of all rows and ignores the "500" part.

Am I missing something? If someone could post a alternate code snippet, that would be great.

Thanks in advance,

Clint

Hi Clint,

Try this expression

Code Snippet

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Best Regards,

Rajiv

|||

Thanks that works well..as an after thought, I need to add something in that would separate on of the field that has a different threshhold. Its grouped up and all but one has the 500 threshold count but one has 1000. Any ideas on how to separate it out?

I was thinking

Code Snippet

=Count(iif (Fields!ResponseTime.Value="QMEN" > 1000,1,nothing)) or Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

But that errored out.|||

Need a second iff. Try just wrapping it around the timeout value

(I can't see your original post, so I'm just going to alter the innermost part of it. I don't think you meant the responsetime = QMEN..)

. . . > iif(XXXX.Value="QMEN", 1000, 500) . . .

|||

How would I incorporate that into?...

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Thanks.

|||

=Count(iif (Fields!ResponseTime.Value > iif(XXXX.Value="QMEN", 1000, 500) , 1, nothing))

Where XXXX is whatever field has the value QMEN

|||Awsome. Thanks so much. the worked perfectlysql

Count of Invoices for Each Hour

This is actually a MySQL query, but I think it's generic enough that any SQL flavour should help.

I need to get the number of invoices per hour between 7am to 9pm and "the rest" (for each day of a week). I thought I knew what I was doing, but I'm getting the total transactions for the day placed in an hour's field (and not any particular hour, that I can tell)

Here's my query:

SELECT
DAYNAME(TransDt) As 'Day'
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN COUNT(InvNum) ELSE 0 END) as NumTrans7
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN COUNT(InvNum) ELSE 0 END) as NumTrans8
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 9 THEN COUNT(InvNum) ELSE 0 END) as NumTrans9
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 10 THEN COUNT(InvNum) ELSE 0 END) as NumTrans10
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 11 THEN COUNT(InvNum) ELSE 0 END) as NumTrans11
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 12 THEN COUNT(InvNum) ELSE 0 END) as NumTrans12
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 13 THEN COUNT(InvNum) ELSE 0 END) as NumTrans13
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 14 THEN COUNT(InvNum) ELSE 0 END) as NumTrans14
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 15 THEN COUNT(InvNum) ELSE 0 END) as NumTrans15
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 16 THEN COUNT(InvNum) ELSE 0 END) as NumTrans16
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 17 THEN COUNT(InvNum) ELSE 0 END) as NumTrans17
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 18 THEN COUNT(InvNum) ELSE 0 END) as NumTrans18
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 19 THEN COUNT(InvNum) ELSE 0 END) as NumTrans19
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 20 THEN COUNT(InvNum) ELSE 0 END) as NumTrans20
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 21 THEN COUNT(InvNum) ELSE 0 END) as NumTrans21
,(CASE WHEN (DATE_FORMAT(TransDt, '%H') < 7) OR (DATE_FORMAT(TransDt, '%H') > 21) THEN COUNT(InvNum) ELSE 0 END) as NumTransOther
,COUNT(InvNum) AS TotalTrans
FROM
tblTransactions
WHERE
(StoreNum = 123)
and (TransDt >= '2006-12-04 01:00:00')
and (TransDt <= '2006-12-11 00:59:59')
group by
DAYNAME(TransDt)
ORDER BY
TransDt;

What did I do wrong?

TIATry SUM() instead of COUNT():
SELECT
DAYNAME(TransDt) As 'Day'
,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN 1 ELSE 0 END) as NumTrans7
,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN 1 ELSE 0 END) as NumTrans8
,...etc...
:shocked:|||That was it. Thanks.

Count of Columns <> 0

How would you count the number of columns with a value not equal to 0 for
each row in a table?

Thanks!

Joe"Joe User" <joe@.user.com> wrote in message
news:c4s5vp$6je$1@.tribune.mayo.edu...
> How would you count the number of columns with a value not equal to 0 for
> each row in a table?
> Thanks!
> Joe

Here's one way:

select PrimaryKeyColumn,
case when col1 = 0 then 0 else 1 end +
case when col2 = 0 then 0 else 1 end +
case when col3 = 0 then 0 else 1 end +
...
case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
from
dbo.MyTable

Simon|||Excellent!

Thanks!

Next question....
How does someone relatively new to tsql learn this sort of thing?

TIA

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4071a1c0$1_3@.news.bluewin.ch...
> "Joe User" <joe@.user.com> wrote in message
> news:c4s5vp$6je$1@.tribune.mayo.edu...
> > How would you count the number of columns with a value not equal to 0
for
> > each row in a table?
> > Thanks!
> > Joe
> Here's one way:
> select PrimaryKeyColumn,
> case when col1 = 0 then 0 else 1 end +
> case when col2 = 0 then 0 else 1 end +
> case when col3 = 0 then 0 else 1 end +
> ...
> case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
> from
> dbo.MyTable
>
> Simon|||"Joe User" <joe@.user.com> wrote in message
news:c4sa4g$c6p$1@.tribune.mayo.edu...
> Excellent!
> Thanks!
> Next question....
> How does someone relatively new to tsql learn this sort of thing?
> TIA

<snip
Get a good book or two - there are some suggestions here:

http://vyaskn.tripod.com/sqlbooks.htm

But don't forget Books Online itself - it's very helpful to read through the
TSQL reference part. I don't mean read every word (unless you have a lot of
time on your hands...), but it helps to have an idea of what's available in
the language. Even if you only vaguely remember what a keyword does, or if
you only remember the name, you can always look it up. The list of functions
is another useful page to review, for the same reason. The
SELECT/INSERT/UPDATE/DELETE entries are very important, as are the CREATE
XXXX entries - all of them are linked to lots of related information, so you
can go into as much detail as you want.

Simon|||>> How would you count the number of columns with a value not equal to
0 for each row in a table? <<

SELECT keycol,
ABS(SIGN(col1)) +
ABS(SIGN(col2)) +
ABS(SIGN(col3)) + .. AS non_zero_tally
FROM Foobar;

count occurrence of character in a field using SQL

Is there a function that will enable me to count the number of instances a
particular character is in a feild? For instance...if I have a field named
number with a record with characters such as 00000111100000 and I what to do
a function that tells me the number of times the number 1 shows up in the
number field of that record. The result would be 4. Is this possible?SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
> Is there a function that will enable me to count the number of instances a
> particular character is in a feild? For instance...if I have a field
> named
> number with a record with characters such as 00000111100000 and I what to
> do
> a function that tells me the number of times the number 1 shows up in the
> number field of that record. The result would be 4. Is this possible?|||Perfect! Thank you!
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
>
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
>
>

Count Occurances in a column

Is there a way to count the number of times a character or string
appears in a column in a table row/column?
Example, tblTable has column StringData varchar(1000)
StringData contains the value "Mary Had A Little Lamb Lamb and Then It
Died"
I want to know how many times "Lamb" appears in this string.
Any help is appreciated.
ThanksOne popular way to do this is:
SELECT ( LEN( stringdata ) -
LEN( REPLACE( stringdata, 'Lamb', '' ) ) ) / LEN( 'Lamb' )
FROM tbl ;
Alternatively, you can use a table of sequentially incrementing numbers and
construct a generic logic using SUBSTRING functions too.
Anith|||laurenq uantrell wrote:
> Is there a way to count the number of times a character or string
> appears in a column in a table row/column?
> Example, tblTable has column StringData varchar(1000)
> StringData contains the value "Mary Had A Little Lamb Lamb and Then It
> Died"
> I want to know how many times "Lamb" appears in this string.
> Any help is appreciated.
> Thanks
DECLARE @.str VARCHAR(1000)
SET @.str= 'Lamb'
SELECT LEN(REPLACE(stringdata,@.str,@.str+'_'))-LEN(stringdata)
FROM tbltable;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||LEN has its problems, so here's one with DATALENGTH:
declare @.text nvarchar(4000)
declare @.string nvarchar(4000)
set @.text = N'Mary Had A Little Lamb Lamb and Then It Died'
set @.string = N'Lamb'
select (datalength(@.text) - datalength(replace(@.text, @.string, N''))) /
datalength(@.string)
ML
http://milambda.blogspot.com/|||Yup, that is right.
Anith|||DECLARE @.StringData varchar(1000)
DECLARE @.counter int
SET @.StringData = 'Mary Had A Little Lamb Lamb and Then It Died'
SET @.counter = 0
WHILE PATINDEX('%Lamb%',@.StringData) <> 0
BEGIN
SET @.counter = @.counter + 1
SET @.StringData = STUFF(@.StringData, PATINDEX('%Lamb%',@.StringData), 4, '')
END
PRINT @.counter
If you want to do this for all rows in a table, one way to accomplish this
is to put the above into a (gasp!) cursor:
DECLARE @.pkcol int --Change this to match that datatype of your PK column
DECLARE @.StringData varchar(1000)
DECLARE @.counter int
CREATE TABLE #results (pkcol int, StringCount int) --same here
DECLARE my_cursor CURSOR STATIC FORWARD_ONLY
FOR
SELECT pkcol, StringData FROM tblTable
FETCH NEXT FROM my_cursor INTO @.pkcol, @.StringData
WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
SET @.counter = 0
WHILE PATINDEX('%Lamb%',@.StringData) <> 0
BEGIN
SET @.counter = @.counter + 1
SET @.StringData = STUFF(@.StringData, PATINDEX('%Lamb%',@.StringData), 4,
'')
END
INSERT INTO #results VALUES (@.pkcol, @.counter)
FETCH NEXT FROM my_cursor INTO @.pkcol, @.StringData
END
SELECT pkcol, StringCount FROM #results
"laurenq uantrell" wrote:

> Is there a way to count the number of times a character or string
> appears in a column in a table row/column?
> Example, tblTable has column StringData varchar(1000)
> StringData contains the value "Mary Had A Little Lamb Lamb and Then It
> Died"
> I want to know how many times "Lamb" appears in this string.
> Any help is appreciated.
> Thanks
>|||Mark,
A single SELECT with a table of numbers may do better than a cursor :
SELECT COUNT(*)
FROM Nbrs
WHERE SUBSTRING( @.stringdata, n, LEN( 'Lamb' ) ) = 'Lamb' ;
Anith|||There's only crap on TV, so...
http://milambda.blogspot.com/2006/0...th-strings.html
ML
http://milambda.blogspot.com/|||On 16 Feb 2006 14:11:22 -0800, laurenq uantrell wrote:

>Is there a way to count the number of times a character or string
>appears in a column in a table row/column?
>Example, tblTable has column StringData varchar(1000)
>StringData contains the value "Mary Had A Little Lamb Lamb and Then It
>Died"
>I want to know how many times "Lamb" appears in this string.
>Any help is appreciated.
>Thanks
Hi laurenq,
SELECT ( DATALENGTH(StringData)
- DATALENGTH(REPLACE(StringData, 'Lamb', '')) )
/ DATALENGTH('Lamb')
FROM tblTable
Hugo Kornelis, SQL Server MVP|||Thanks David and all other posters for these solutions.
lq

Count number of visitor

I have a database of visitors as follows:
Name; Begdate, Enddate
I would like to count the number of visitors every day over a six month
period.
Is there an easy way to do this using SQL?See if this helps:
-- count number of orders every 6 months
use northwind
go
select
colA,
count(*)
from
(
select
convert(char(4), orderdate, 112) + ' - ' + ltrim((month(orderdate) % 2) + 1)
from
orders
) as t(colA)
group by
colA
order by
colA
go
AMB
"Mustapha Amrani" wrote:

> I have a database of visitors as follows:
> Name; Begdate, Enddate
> I would like to count the number of visitors every day over a six month
> period.
> Is there an easy way to do this using SQL?
>
>|||Thanks for the reply, I am afraid that is not what I need. I have a
programme over 6 month period, I have visitors that come to this programme
for varying periods. I would like to give the administrator a table of
number of visitors for each day of the programme so that she can do the room
booking and monitor the period were the programme is over booked.
At the moment I am itirating over the period and counting the number of
visitors for each day, I was wondering if there is a better way to do this
in SQL.
Jan1; 23
Jan2; 36
jan 3; 31
jan 4; 27
etc...
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:909107DE-5362-4A90-A40E-31E0CF8F9C77@.microsoft.com...
> See if this helps:
> -- count number of orders every 6 months
> use northwind
> go
> select
> colA,
> count(*)
> from
> (
> select
> convert(char(4), orderdate, 112) + ' - ' + ltrim((month(orderdate) % 2) +
> 1)
> from
> orders
> ) as t(colA)
> group by
> colA
> order by
> colA
> go
>
> AMB
> "Mustapha Amrani" wrote:
>|||On Wed, 2 Feb 2005 17:20:36 -0000, Mustapha Amrani wrote:

>I have a database of visitors as follows:
>Name; Begdate, Enddate
>I would like to count the number of visitors every day over a six month
>period.
>Is there an easy way to do this using SQL?
>
Hi Mustapha,
First, create a calendar table. See www.aspfaq.com/2519.
When your calendar table is ready, try if this query works for you:
SELECT c.dt, COUNT(*)
FROM Calendar AS c
LEFT JOIN Visitors AS v
ON v.Begdate <= c.dt
AND v.Enddate >= c.dt
GROUP BY c.dt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Woops, I forgot to add the WHERE clause.
The query should have been:
SELECT c.dt, COUNT(*)
FROM Calendar AS c
LEFT JOIN Visitors AS v
ON v.Begdate <= c.dt
AND v.Enddate >= c.dt
WHERE c.dt >= '20050101' -- If the period you need to report
AND c.dt < '20050701' -- is the first 6 months of 2005
GROUP BY c.dt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Then Hugo's post should do it.
AMB
"Mustapha Amrani" wrote:

> Thanks for the reply, I am afraid that is not what I need. I have a
> programme over 6 month period, I have visitors that come to this programme
> for varying periods. I would like to give the administrator a table of
> number of visitors for each day of the programme so that she can do the ro
om
> booking and monitor the period were the programme is over booked.
> At the moment I am itirating over the period and counting the number of
> visitors for each day, I was wondering if there is a better way to do this
> in SQL.
> Jan1; 23
> Jan2; 36
> jan 3; 31
> jan 4; 27
> etc...
> Thanks
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:909107DE-5362-4A90-A40E-31E0CF8F9C77@.microsoft.com...
>
>sql

count number of rows in asp.net

hey, how would i count the number of rows, with out using a loop??
thanks, JustinSELECT COUNT(*) FROM MyTable|||SELECT COUNT(*) FROM MyTable
ok, so i added COUNT(*) to my SELECT * statement, and i get this error:
Exception Details: System.IndexOutOfRangeException: booking_id...and the only value in booking_id is 1, and there is only one result returned...

Count number of pages sent to printer

Is there anyway to count number of pages sent to the printer, to track them?Do you mean in Reporting Service ? Then you are in the wrong forum ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

More specifically, for example: print the contents from a webbrowser, then show in a masseage box:

"X pages sent to printer"

count number of lines in a clumn

The SP returns a column which contains
Error: 1212121
Error: 3434fsfa
Error: fdfdfd
Each above line has Char(10) + Char(13) attached so they will start a new line
Now I want to know the number of errors returns by using pattern "Error". Is
there any function I can use to do this?
thanks.In SQL you could do something like:
(LEN(columname) - LEN(REPLACE(columnname, 'Error' , '') ) / 5
I am not sure what the equivalent VB.Net syntax would look like to do it in
SRS.
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:B2FA56E5-AD24-4100-AD8B-B4CD7E771606@.microsoft.com...
> The SP returns a column which contains
> Error: 1212121
> Error: 3434fsfa
> Error: fdfdfd
> Each above line has Char(10) + Char(13) attached so they will start a new
> line
> Now I want to know the number of errors returns by using pattern "Error".
> Is
> there any function I can use to do this?
> thanks.

count number of connections in sql

This is probably a newbie quesiton, but I would like to be able to count how
many users connect to my db for evvery 30 minutes. How may I do this within
sql 2k?
Thank you in advanceSELECT COUNT(DISTINCT spid) FROM master..sysprocesses WHERE spid > 50
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:06F88966-654C-449D-8085-9F87DC7455DF@.microsoft.com...
> This is probably a newbie quesiton, but I would like to be able to count
> how
> many users connect to my db for evvery 30 minutes. How may I do this
> within
> sql 2k?
> Thank you in advance|||Thank you for responding! I think that this code will give me a snapshot of
how many are connected at a given time, but I need to know how many total
have connected in the last 30 minutes. perhaps I can place a trigger on this
table that updates a count in another table whenever a new process (where
spid>50) is spawned?
"Aaron [SQL Server MVP]" wrote:

> SELECT COUNT(DISTINCT spid) FROM master..sysprocesses WHERE spid > 50
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:06F88966-654C-449D-8085-9F87DC7455DF@.microsoft.com...
>
>|||No trigger on system tables, sorry. You will have to poll the table
constantly. Plus, if I connect and get assigned spid 52, you poll, then I
disconnect and someone else connects and gets assigned 52, you won't see the
difference unless you compare the deltas in all columns (which still might
not yield a discrepancy).
You might consider tracking this more from the application side, e.g. if you
have a GUI where people are logging in, then in the stored procedure that
checks their credentials, log the connection in some table. SQL Server
isn't going to provide you this kind of information directly.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:D84485D0-59F2-4298-89DC-04AFDC5B29DA@.microsoft.com...
> Thank you for responding! I think that this code will give me a snapshot
> of
> how many are connected at a given time, but I need to know how many total
> have connected in the last 30 minutes. perhaps I can place a trigger on
> this
> table that updates a count in another table whenever a new process (where
> spid>50) is spawned?
> "Aaron [SQL Server MVP]" wrote:
>|||Thank you for your help!!
"Aaron [SQL Server MVP]" wrote:

> No trigger on system tables, sorry. You will have to poll the table
> constantly. Plus, if I connect and get assigned spid 52, you poll, then I
> disconnect and someone else connects and gets assigned 52, you won't see t
he
> difference unless you compare the deltas in all columns (which still might
> not yield a discrepancy).
> You might consider tracking this more from the application side, e.g. if y
ou
> have a GUI where people are logging in, then in the stored procedure that
> checks their credentials, log the connection in some table. SQL Server
> isn't going to provide you this kind of information directly.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:D84485D0-59F2-4298-89DC-04AFDC5B29DA@.microsoft.com...
>
>

Count Item Number

Hello;

Which is the easy way to do this:

I have 2 tables:Work_Order_Header and Work_Order_Detail.

I need to generate the Item Number from 1 to "qty of items" when I make the JOIN on WorkOrderNumber.

Example:

WorkOrderNumber WorkOrderItem WorkOrderAmt

122 1 10.00

122 2 15.25

122 3 24.37

How I generate the WorkOrderItem using a select with a JOIN on the 2 tables?

if you are working on SQL Server 2005, you can use the following (samples tables from Northwind database, as you provided no DDL and sample data)

SELECT ROW_NUMBER() OVER (PARTITION BY O.OrderId order by O.OrderDate),O.*,OD.* FROm

Orders O

INNER JOIN [Order details] OD

ON O.orderid = od.OrderID

WHERE O.orderid <=10251

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you Jens, but I am working with SQL 2000 I forgot to mentioned.|||A sample you be something like:

SELECT (SELECT COUNT(*) FROM [Order details] OD2 WHERE OD2.orderID = O.OrderID AND OD2.ProductID >=OD.ProductID) AS Number ,O.*,OD.* FROm

Orders O

INNER JOIN [Order details] OD

ON O.orderid = od.OrderID

WHERE O.orderid <=10251

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Did that solve your problem ?sql

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.

count iif

Hope someone can help.
I am trying to do a count where a field is equal to a certain number of
values.
The example below doesn't work but this is the idea.
=Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
Fields!JobNo.Value, Nothing))
I wondered if someone could point me in the right direction.
Thanks PaulSELECT SUM(CASE WHEN VisitType IN ('RTF', 'ARF', 'ERF', 'WOK') THEN 1 ELSE 0
END)
FROM your_table
--
Jacco Schalkwijk
SQL Server MVP
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:6D921197-A432-4C19-B5AE-1958B94CE795@.microsoft.com...
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Try the posted case statement first, but if that doesn't work, you have to
spell out your fields again.
Like:
=Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
Fields!JobNo.Value, Nothing))
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"pcalv" wrote:
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Thanks for the replies.
Catadmin i used your suggestion which worked great.
Cheers Paul
"Catadmin" wrote:
> Try the posted case statement first, but if that doesn't work, you have to
> spell out your fields again.
> Like:
> =Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
> or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
> Fields!JobNo.Value, Nothing))
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > I am trying to do a count where a field is equal to a certain number of
> > values.
> >
> > The example below doesn't work but this is the idea.
> >
> > =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> > Fields!JobNo.Value, Nothing))
> >
> > I wondered if someone could point me in the right direction.
> >
> > Thanks Paul|||Glad I could help. @.=)
Catadmin
"pcalv" wrote:
> Thanks for the replies.
> Catadmin i used your suggestion which worked great.
> Cheers Paul
>

Count how many times a character appeared in a string

Hi all,
I'm having trouble googling this problem ...
Would anyone know the easiest way to obtain the number of times a
character appeared in a given string?
Thanks
ACA WHILE loop will do the trick. Just for fun though, here's a set-based
approach that uses the good old numbers table:
SELECT TOP 100 number = IDENTITY(INT, 1, 1)
INTO #numbers
FROM syscomments a1
CROSS JOIN syscomments a2
ALTER TABLE #numbers
ADD CONSTRAINT pk_number
PRIMARY KEY CLUSTERED (number)
DECLARE @.string VARCHAR(20)
DECLARE @.letter CHAR(1)
SELECT @.string = 'THIS IS A TEST'
SELECT @.letter = 'S'
SELECT @.letter AS letter, COUNT(*) AS occurrences
FROM #numbers n
WHERE SUBSTRING(@.string, n.number, 1) = @.letter
DROP TABLE #numbers
"AC" <anchi.chen@.gmail.com> wrote in message
news:1150341230.216963.54010@.g10g2000cwb.googlegroups.com...
> Hi all,
> I'm having trouble googling this problem ...
> Would anyone know the easiest way to obtain the number of times a
> character appeared in a given string?
>
> Thanks
> AC
>|||Thanks Mike. I'll give that a try.
Thanks again.
AC|||DECLARE @.foo VARCHAR(64);
DECLARE @.c VARCHAR(1);
SET @.foo = 'How many qs are in this qqq qqq blat?';
SET @.c = 'q';
SELECT Number_Of_Qs = LEN(@.foo) - LEN(REPLACE(@.foo, @.c, ''));
"AC" <anchi.chen@.gmail.com> wrote in message
news:1150341230.216963.54010@.g10g2000cwb.googlegroups.com...
> Hi all,
> I'm having trouble googling this problem ...
> Would anyone know the easiest way to obtain the number of times a
> character appeared in a given string?
>
> Thanks
> AC
>|||This is such a smart and short solution.
Thanks Aaron
Aaron Bertrand [SQL Server MVP] wrote:

> DECLARE @.foo VARCHAR(64);
> DECLARE @.c VARCHAR(1);
> SET @.foo = 'How many qs are in this qqq qqq blat?';
> SET @.c = 'q';
> SELECT Number_Of_Qs = LEN(@.foo) - LEN(REPLACE(@.foo, @.c, ''));
>
>

Count Function

I am trying to design a blog and I want to have the typical setup where you see something like this:

Comments(some number), where the some number specifies the number of comments for a particular entry. I am thinking about two different approaches:

#1 is combining the count function into my normal query

objCmd = new OleDbCommand("SELECT TOP 10 Blog.*, BlogCategories.* FROM Blog INNER JOIN BlogCategories ON Blog.categoryID = BlogCategories.categoryID WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07' ORDER BY Blog.EntryDate DESC", objConn);

I would need to add an additional inner join to get to the table, BlogComments, to be able to count the correct number of comments for a specific entry, and obviously incorporate the Count function into my query as well.

Option #2 is creating a new column in my Blog datatable and just keep track of the number of comments by adding or subtracting (++ or --) from a record when a new comment is added or deleted. (I think this is easier but can have obvious problems if non standard changes are made to the blog).

Your best bet is like your 1st option, but instead of a join you use a subquery in your select list.

Add something like the following as your third column in your select list, and wrap parentheses around it

SELECT COUNT(*) FROM BlogComments WHERE BlogID = Blog.BlodID

|||

You can also send two separate statements in your command ("select top 10, etc......order by desc; select count(*)....", objConn)

and then you'll have two tables in your dataset or you can use .NextResult() on the Reader object.

|||

I tried your suggestion and I get an error: The multi-part identifier "Blog.BlogID" could not be bound.

Do I need an alias? Do I need to declare the value?

objCmd = new OleDbCommand("SELECT TOP 10 Blog.*, BlogCategories.* FROM Blog INNER JOIN BlogCategories ON Blog.categoryID = BlogCategories.categoryID WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07' ORDER BY Blog.EntryDate DESC; SELECT COUNT(*) AS NumComments FROM BlogComments WHERE BlogCID = Blog.BlogID", objConn);

|||

It was a silly assumption that there would be something as useful as a unique way to identify each Blog thread.

Please post your table DDL and sample data in the form of INSERT statements.

|||

SELECT TOP 10 Blog.*, BlogCategories.*, (select count(*) from BlogComments where BlogComments.<BlogKey> = Blog.<BlogKey>) as CommentCount

FROM Blog

INNER JOIN BlogCategories

ON Blog.categoryID = BlogCategories.categoryID

WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07'

ORDER BY Blog.EntryDate DESC

Now, you need to fill in the BlogKey values, whatever they are (and there could be > 1 columns in the key.) If this doesn't make sense then you really do need to post your structures...

|||You are an optimist Louis. <BlogKey> is as likely to be comprehended as [BlogComments] or [BlogID]|||

Each query in the command string needs to be able to execute on its own.

Apparently Blog.BlogID isn't in the table you're referencing.

|||

Here are my tables and the columns within them:

Blog: BlogID(smallint), BlogEntry(varchar),State(varchar),Body(ntext), FirstName(varchar), LastName(varchar), CategoryID(varchar), Email(varchar), EntryDate(datetime)

BlogCategories: CategoryName(nchar), CategoryID(varchar)

BlogComments: BlogCID(smallint), Comment(ntext), FirstName2 (varchar), LastName2(varchar), Email2(varchar), CommentDate(datetime)

FYI: In my original post I had listed BlogID in both the BlogComments and Blog tables. I later changed the BlogComments' BlogID to BlogCID to ensure there was no conflict.

As far as how my pages are setup, I have a page that displays an entry, all comments connected to it, and additional comments can be entered. I have another page where new entries can be added. Then the third page, the main page, where I want to have a listing of entries and the number of comments for each attached to it is the one that is not working.

|||

Thanks, that is very helpful.

Now an additional question.

What links a Blog with its Comments?

(It seems like BlogComments 'should' have the BlogID as a Foriegn Key.)

|||both BlogID and BlogCID are primary keys and this is the information that connects the entry and the comments. On my other page where I display the comments along with the corresponding entry I have two separate functions, the connecting factor is that I do a URL query to collect a variable designating the entry and then both the entry and subsequent comments are each independently called based on that variable. So, joining the tables has been a problem I have been able to avoid until now.|||

So now I'm confused here.

If BlogCID is a Primary Key for the BlogComments table, and if BlogID is a Primary Key for the Blogs table, AND they are used to link the two tables, that would mean there could only be ONE comment per blog. That doesn't seem quite right.

|||Sorry, you are correct, there is no primary key for the Comments table.|||

Thanks for the clarification.

You would 'best' position the BlogComments table if you were to add a Primary key, perhaps an IDENTITY field.

And name it BlogCommentsID. (A good naming practice is {TableName}ID for IDENTITY fields.)

Without a Primary Key (or other method) to specifically identify each individual row, the row would NOT be editable.

I would rename the existing BlogCID to BlogID -that makes it easier to 'see' that there is a PK-FK relationship with the Blogs table.

Then this revision of your previous query 'should' work as you want. (NOTE: I have assumed renaming the BlogCID to BlogID.)

SELECT TOP
10 b.*,
bc.*,
bcom.ThreadCnt
FROM Blog b
INNER JOIN BlogCategories bc
ON b.categoryID = bc.categoryID
JOIN (SELECT
BlogID,
ThreadCnt = count(1)
FROM blogComments
GROUP BY BlogID
) bcom
ON b.BlogID = bcom.BlogID
WHERE b.EntryDate BETWEEN '7/1/06' AND '12/31/07'
ORDER BY b.EntryDate DESC;

|||I made the update and the page does load (I do not get an error screen), but the area on the page for the data is completely blank.