Count records when RecordSource is a Stored Procedure

I have a stored procedure named mySP that looks basically like this:
Select Field1, Field2
From tblMyTable
Where Field 3 = 'xyz'

What I do is to populate an Access form:
DoCmd.Openform "frmMyFormName"
Forms!myFormName.RecordSource = "mySP"

What I want to do in VBA is to open frmContinuous(a datasheet form) if
mySP returns more than one record or open frmDetail if mySP returns
only one record.

I'm stumped as to how to accomplish this, without running mySP twice:
once to count it and once to use it as a recordsource.
Thanks,
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404260611.33a36012@.posting.google.c om...
> I have a stored procedure named mySP that looks basically like this:
> Select Field1, Field2
> From tblMyTable
> Where Field 3 = 'xyz'
> What I do is to populate an Access form:
> DoCmd.Openform "frmMyFormName"
> Forms!myFormName.RecordSource = "mySP"
> What I want to do in VBA is to open frmContinuous(a datasheet form) if
> mySP returns more than one record or open frmDetail if mySP returns
> only one record.
> I'm stumped as to how to accomplish this, without running mySP twice:
> once to count it and once to use it as a recordsource.
> Thanks,
> lq

I don't know much about VBA, but if you return the results of the procedure
in an ADO RecordSet object, then you should be able to count the rows on the
client side, and format the data accordingly. You might get a better answer
in an Access newsgroup, though.

Simon

count records using a max value in another field

Please help. I am struggling.

table

cards year org

Q123 Q MO

Q236 Q MO

Q569 Q MO

R456 R MO

Q789 Q MO

R482 R MO

First I need to find the max value of the year field then use that max value to count the cards associated with that max value where org is a parameter from the user.

So the result would be 2. I need a SQL query to give me these results.

R year is the max value and there are two of those records. I hope this makes sense.

Something like this (works in SQL 2000 and SQL 2005):

Code Snippet

SELECT TOP 1

Year,

Count = count( Year )

FROM MyTable

GROUP BY Year

ORDER BY Year DESC

|||

Code Snippet

SELECT max(activeyear)AS activeyear, COUNT(cardnum) AS cardCnt
FROM camcards

WHERE org = @.org)

This is my original query but it gives me the count for all the years regardless of my max(year). The user would be entering the value in for org. I should get back one line so there is not need to to have an order by.

so in my original message the result should be

activeyear = R and cardCnt = 2

I am sorry Arnie, but your suggestion does not work or make sense to me. I don't think you can put an equal sign in the SELECT part of the statement.

|||

Try:

select a.year, count(*)

from dbo.t1 as a inner join (select c.org, max(c.year) from dbo.t1 as c where c.org = @.org group by org) as b

on a.org = b.org and a.year = b.year

group by a.year

go

select year, count(*)

from dbo.t1

where org = @.org and year = (select max(b.year) from dbo.t1 as b where org = @.org)

order by year

go

select top 1 year, count(*)

from dbo.t1

where org = @.org

gropu by year

order by year desc

go

AMB

|||

Select top 1 year, (SELECT count(1) from table where year = t.yearr and org = t.org)

from table t

where org = 'mo'

order by year desc

|||

Actually, Theresa, the code I provided you does indeed work. I understand that you my not yet have the exprience to follow how it works, and I'll try to help. I've even added the extra WHERE clause to filter for the Org by user input.

Below is demonstration code that creates a sample table (named @.MyTable), and then populates the table with your data (I tossed in a couple of extra rows to verify that they would not be included in the result.) I suggest that you copy and paste code into your query window and execute it to verify that it works, THEN change the code, substituting the table name for your tablename.

I don't think you can put an equal sign in the SELECT part of the statement.

You are a little 'confused' about using equals signs in a SELECT statement, and that is just from inexperience. Actually, there are a couple of ways that one may choose to use equals signs in the SELECT. First, to assign a column value to a variable (e.g., SELECT @.ClientID = ClientID FROM ... ), and also to assign an 'alias' for a 'missing' column name or to rename a column in the resultset (e.g, Count = count( Year ) ). Without the 'alias', the column representing the aggragate value COUNT() would not have a name. That is often OK, and sometimes for illustration purposes, it is useful.

At the bottom is the actual results from executing all of this sample code in my query window. I think that matches what you said that you wanted.

As you progress in your learning SQL Server, you will discover that it is considered a big faux paux to use reserved words as table and column names (e.g., Year). When you do that you may 'confuse' matters, so to prevent such 'confusion', it is necessary to use square brackets ( [ ] ) around those inappropiately named objects. You may wish to refer to Books Online, Topic: Reserved Words.

I hope that this helps you, and good luck with your travels down the SQL Server 'path'.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Cards varchar(10),
[Year] char(1),
Org char(2)
)

INSERT INTO @.MyTable VALUES ( 'Q123', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q236', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q569', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R456', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q789', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R482', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'ABC1', 'S', 'NO' )
INSERT INTO @.MyTable VALUES ( 'XYZ2', 'T', 'NO' )

DECLARE @.Org char(2)
SET @.Org = 'MO'

SELECT TOP 1
ActiveYear = [Year],
CardCnt = count( [Year] )
FROM @.MyTable
WHERE Org = @.Org
GROUP BY [Year]
ORDER BY [Year] DESC

ActiveYear CardCnt
- --
R 2

|||Thanks to all who have replied to thread.

Count records manually

Hi everyone, I'm a newbie with this Crystal Reports thing and I was wondering if there is anyone who can help me.

My table has a field named obsolete, depending on the type of "obsolete" I would like to count the number of ocurrencies in my table.

I know I could do this by grouping the field but, I need to group with other 4 fields and I've tried it like that but I guess that the only way that I'll be able to retrieve the data the way I want it is manually. So, is there any if statement or while statement that I could use?

Thanx for any repliesI guess you could try to use if statements in suppressed formulas (look up information on running totals in CR).

I had a similar requirement and found it easier to decode the values in my view (I'm using Oracle PL/SQL).

decode(classId,1,trafficcount,0) class_1,
decode(classId,2,trafficcount,0) class_2,

Ie. If the classId is 1, put the trafficcount in column called class_1, else put 0.|||Thanx for your reply kristyw. Im using informix, I'm not quite sure I can use the decode function, or is it a CR function?

Count Records in sqlcommand or sqladapter?

Hi everyone? I have a small problem here, I want to count the records found under the following method:

Public Function ValidateAssembly(ByVal assynoAs String)As DataSetDim SQLConnAs SqlConnection =New SqlConnection(Connstr)Dim adapterAs SqlDataAdapter =New SqlDataAdapterDim dsVendorInfoAs DataSet =New DataSet("AssemblyHeader")Dim cmdAs System.Data.SqlClient.SqlCommand =New System.Data.SqlClient.SqlCommandDim BMItemnoAs New SqlParameter("@.v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open()Try With cmd .CommandText ="SELECT * FROM dbo.cfn_bom_get_assy(@.v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.TextEnd With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo)Catch xAs ExceptionEnd Try SQLConn.Close()Return dsVendorInfoEnd Function

I want to see if this can be done and passed to the actual dataset? Is this possible? Or do I need to pass the found results from my query to a sqlreader? Also, Im I forced to put my colums together in a DataTable before actually binding to the DataSet?

 Thanks everyone! 

If I understand what you are looking for, you want the count of the number of records returned in your Dataset. All you have to do for that is:

dsVendorInfo.Tables(0).Rows.Count

|||Right, and how can I attach that to my dataset? Create a new column within the dataset that contains that value?|||

Why would you want to attach that to your dataset?? Anywhere you use the dataset you can retrieve the count.

|||

Scratch that. My intent is to simply pass the record count to a label. I am using an ajax control to extract data from this function and then the dataset populates all these labels in my form. I have a record count label which I have to also populate. So I was just thinking to add an extra column with a record count value but seems overhead to me. I created a new method that will just be used to populate that one label. My problem is that the record count does not show on your suggested "dsVendorInfo.Tables(0).Rows.Count" formula. I placed a break point on this line and its not being populated andI know its passing values (I am running a trace on the query). Here is my code:

<AjaxPro.AjaxMethod()> _Public Function ValidateAssembly(ByVal assynoAs String)As DataSetDim SQLConnAs SqlConnection =New SqlConnection(Connstr)Dim adapterAs SqlDataAdapter =New SqlDataAdapterDim dsVendorInfoAs DataSet =New DataSet("AssemblyHeader")Dim cmdAs System.Data.SqlClient.SqlCommand =New System.Data.SqlClient.SqlCommandDim BMItemnoAs New SqlParameter("@.v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open()Try With cmd .CommandText ="SELECT * FROM dbo.cfn_bom_get_assy(@.v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.TextEnd With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo)Dim intCountAs Integer intCount = dsVendorInfo.Tables(0).Rows.Count 'showing empty when called upon RecordCount(intCount)Catch xAs ExceptionEnd Try SQLConn.Close()Return dsVendorInfoEnd Function Private Sub RecordCount(ByVal intNumAs Integer) lblRecordCount.Text = intNumEnd Sub

|||URGGHGH Scratch that too. I was passing my variable as an integer. It has to be passed as a string. Working beautifully now. Thanks for the help! :)

count records in several months

Hi,

I've a small problem. I have a table in which one column is date. I want to
count the records for statiscs in a temptable grouped by months lets say 12
months back.
e.g.
month 1 counts 164 rec month 2 counts 87 records and so on.
I tried to solve this like this with a function SELECT COUNT(*) FROM TABLE
WHERE DATEDIFF(m,Col1,GETDATE())=@.counter.
But I don't know how to get this thing count from 0 up to 11 to get this
thing recursive.
Does anyone know how to tackel my problem? I wouls apreciate any answer.
Greetz to you allYou need counts based on months? If your date column is datetime datatype,
something like this could work:

select count(*) as No, month(col1) as month, year (col1) as year
from table
where datediff(m, col1, getdate()) <= 12 -- if you want only last 12 months
group by month(col1) as month, year (col1) as year

If you posted create statements and some sample data I could test this...

MC

"Sjef ten Koppel" <sjeftenkoppel@.home.nlwrote in message
news:er97ad$qkb$1@.news6.zwoll1.ov.home.nl...

Quote:

Originally Posted by

Hi,
>
I've a small problem. I have a table in which one column is date. I want
to count the records for statiscs in a temptable grouped by months lets
say 12 months back.
e.g.
month 1 counts 164 rec month 2 counts 87 records and so on.
I tried to solve this like this with a function SELECT COUNT(*) FROM TABLE
WHERE DATEDIFF(m,Col1,GETDATE())=@.counter.
But I don't know how to get this thing count from 0 up to 11 to get this
thing recursive.
Does anyone know how to tackel my problem? I wouls apreciate any answer.
Greetz to you all

|||Sjef ten Koppel (sjeftenkoppel@.home.nl) writes:

Quote:

Originally Posted by

Oops you are fast! thank you.
I could send you a create sql but I don't know how to extract sample data
from my db see att.

It does not have to be real-world data, you could just fill in some
sample data, and tell us what the result you want from the sample. That
helps to clarify your question.

But did not the query that Macro posted fit your needs?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Great I tried your solution. It works great. Thank you!!
"MC" <marko.NOSPAMculo@.gmail.comschrieb im Newsbeitrag
news:er983u$64q$1@.ss408.t-com.hr...

Quote:

Originally Posted by

You need counts based on months? If your date column is datetime datatype,
something like this could work:
>
select count(*) as No, month(col1) as month, year (col1) as year
from table
where datediff(m, col1, getdate()) <= 12 -- if you want only last 12
months
group by month(col1) as month, year (col1) as year
>
If you posted create statements and some sample data I could test this...
>
>
MC
>
>
"Sjef ten Koppel" <sjeftenkoppel@.home.nlwrote in message
news:er97ad$qkb$1@.news6.zwoll1.ov.home.nl...

Quote:

Originally Posted by

>Hi,
>>
>I've a small problem. I have a table in which one column is date. I want
>to count the records for statiscs in a temptable grouped by months lets
>say 12 months back.
>e.g.
>month 1 counts 164 rec month 2 counts 87 records and so on.
>I tried to solve this like this with a function SELECT COUNT(*) FROM
>TABLE WHERE DATEDIFF(m,Col1,GETDATE())=@.counter.
>But I don't know how to get this thing count from 0 up to 11 to get this
>thing recursive.
>Does anyone know how to tackel my problem? I wouls apreciate any answer.
>Greetz to you all

>
>

|||Hallo,

Probeer dit eens:

select
YYYYMM=
(case
when datepart(m,Col1) < 10
then convert(int,convert(char(4),datepart(yyyy,Col1))+ '0'+convert(char(1),datepart(m,Col1)))
else convert(int,convert(char(4),datepart(yyyy,Col1))+c onvert(char(2),datepart(m,Col1)))
end), count(*) as [COUNT] from Table1

group by

(case
when datepart(m,Col1) < 10
then convert(int,convert(char(4),datepart(yyyy,Col1))+ '0'+convert(char(1),datepart(m,Col1)))
else convert(int,convert(char(4),datepart(yyyy,Col1))+c onvert(char(2),datepart(m,Col1)))
end)

order by

(case
when datepart(m,Col1) < 10
then convert(int,convert(char(4),datepart(yyyy,Col1))+ '0'+convert(char(1),datepart(m,Col1)))
else convert(int,convert(char(4),datepart(yyyy,Col1))+c onvert(char(2),datepart(m,Col1)))
end)

Groeten,

Wim Venema
delerium@.chello.nl
"Sjef ten Koppel" <sjeftenkoppel@.home.nlwrote in message news:er97ad$qkb$1@.news6.zwoll1.ov.home.nl...
Hi,

I've a small problem. I have a table in which one column is date. I want to
count the records for statiscs in a temptable grouped by months lets say 12
months back.
e.g.
month 1 counts 164 rec month 2 counts 87 records and so on.
I tried to solve this like this with a function SELECT COUNT(*) FROM TABLE
WHERE DATEDIFF(m,Col1,GETDATE())=@.counter.
But I don't know how to get this thing count from 0 up to 11 to get this
thing recursive.
Does anyone know how to tackel my problem? I wouls apreciate any answer.
Greetz to you allsql

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 Help

Hello I have the following table below. I need to create a query that will list conference, avg. attendance, avg. winning percentage for the current year grouped by conference. For winning percentage I'm assuming I would need to count occurrnces of self score > opp_score then divide that by counting the number of dates entries within that year? ..Dont have a clue on how to express this query - Thanks for any help or suggestions

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));Although I dont have the exact answer are you trying to do the following

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 2
B 12000 9 6
B 8000 2 2
B 16000 3 7

select conf, count(*) / (select count(*) from table)
from table
where self_score > opp_score
group by conf;

This query doesn't work because you need to group the divided query into the same group as the outer query. I would also like to know how this is done.|||Greetings,

I now have the answer :),

conf = conference

select s1.conf, avg_att As "Avg. Att", (wins / total) * 100 As "% Won"
from
(select conf, count(*) As wins
from help
where self_score > opp_score
group by conf) s1,
(select conf, avg(att) As avg_att, count(*) As total
from help
group by conf) s2
where s1.conf = s2.conf;

The average attendance is for the entire conference group regardless of whether or not they won. If you only want average attendance for the games they played then select the avg(att) in the top from-query as opposed to the second.

The sample table I used for this is shown below,

SQL> select * from help;

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 0
B 12000 4 6
B 8000 2 0
B 16000 3 7

And the results generated from the answer query

CONF Avg. Att % Won
---- ---- ----
A 7066.66667 66.6666667
B 12000 33.3333333

Cheers.|||- Thanks for your reply, It appears I left some info out, Ive been trying to adapt what you replied with but still no dice.

I am including the tables and data below. There are some null values as I am only inputing sample data that is needed in the reports.

CREATE TABLE SCHOOL
(school VARCHAR2(30),
conference VARCHAR2(25),
stadium_size NUMBER(6),
ticket_price NUMBER(4,2),
in_state_players NUMBER(2),
outstate_players NUMBER(2),
scholarships NUMBER(2),
graduate NUMBER(20));

INSERT INTO school VALUES
('Indiana Univ.', 'Big Ten', 53000, null, null, null, null, null);
INSERT INTO school VALUES
('Ohio State Univ.', 'Big Ten', 104000, null, null, null, null, null);
INSERT INTO school VALUES
('Penn State Univ.', 'Independent', 80000, null, null, null, null, null);
INSERT INTO school VALUES
('Univ. of Pittsburgh', 'Independent', 51000, null, null, null, null, null);
INSERT INTO school VALUES
('Pondunk Univ.', 'Independent', 44000, null, null, null, null, 35);
INSERT INTO school VALUES
('Violator Univ.', 'Independent', 39000, null, null, null, null, 47);

CREATE TABLE SCHOOL_INCIDENTS
(school VARCHAR2(30),
idate DATE,
incident_code NUMBER(5));

INSERT INTO school_incidents VALUES
('Indiana Univ.',null, 17983);
INSERT INTO school_incidents VALUES
('Ohio State Univ',null, 12891);
INSERT INTO school_incidents VALUES
('Penn State Univ.',null, 17250);

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 43000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 42000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 41000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 40000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 39000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 38000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 37000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 36000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 51000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 50000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 49000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 48000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 47000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 43000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 42000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 41000, 0, 7, null, null);

Below is the basic query I have been working from. I will need to add a column inbetween conference and attendance for "WINNNING PERCENTAGE" which would be calcuated for self_score & opp_score in homegame
then average the attendance column and count the recruiting incidents per conference. The final output should be 2 rows listing the calculated data.

SELECT ALL SCHOOL.CONFERENCE, HOMEGAME.ATTENDANCE "AVG ATTENDANCE", SCHOOL_INCIDENTS.INCIDENT_CODE "RECRUITING INCIDENTS"
FROM SCHOOL, SCHOOL_INCIDENTS, HOMEGAME
WHERE ((SCHOOL.SCHOOL = HOMEGAME.SCHOOL)
AND (HOMEGAME.SCHOOL = SCHOOL_INCIDENTS.SCHOOL));

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Big Ten 46000 17983
Big Ten 45000 17983
Big Ten 44000 17983
Big Ten 43000 17983
Big Ten 42000 17983
Big Ten 41000 17983
Big Ten 40000 17983
Big Ten 39000 17983
Big Ten 38000 17983
Big Ten 37000 17983
Big Ten 36000 17983

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Independent 51000 17250
Independent 50000 17250
Independent 49000 17250
Independent 48000 17250
Independent 47000 17250
Independent 46000 17250
Independent 45000 17250
Independent 44000 17250
Independent 43000 17250
Independent 42000 17250
Independent 41000 17250

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 record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,

>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all[vbcol=seagreen]
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
>
> successful execution of the
> records.
> all|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:

>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Count record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,
Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,
|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,

>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the[vbcol=seagreen]
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all
>
>.
>
|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> successful execution of the
> records.
> all
|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:

>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Count record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>> When I count records from tableA, I got results as about
>> 11M records . But the table actually has only 2M
records.
>> I did count(*), or count (ID), or count(distinct ID),
all
>> give me the same 12M.
>> But I'm sure the table has only 2M. Thanks,
>> It is SQL Server 2000. I did it in Query analyzer.
>>
>> The exact queries are:
>> select count(ID)
>> from tableA
>> select count(*)
>> from tableA
>> select count(Field1)
>> from tableA
>> select count(field2)
>> from tableA
>> select count(*)
>> from tableA
>> select distinct count(ID)
>> from tableA
>> comment: ID is the identity field.
>> I rebuilt/recreated the indexes.
>> They all showed as about 12M
>> select sum(1) from tableA
>> I got about 12M
>> I know the records in 2M for sure. Also when I did
>> select *
>> into temptableA
>> from tableA
>> about 2M rows affected.
>> What could be wrong?
>> Thanks,
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> >--Original Message--
> >Hi,
> >
> >Execute the below command :-
> >
> >sp_spaceused <table_name>,@.updateusage='true'
> >
> >THis will return the exact row count. After the
> successful execution of the
> >above command try executing the
> >
> >select count(*) from table_name
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >"ISD_ERD" <lxwang@.pa1call.org> wrote in message
> >news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> >>
> >> When I count records from tableA, I got results as about
> >> 11M records . But the table actually has only 2M
> records.
> >> I did count(*), or count (ID), or count(distinct ID),
> all
> >> give me the same 12M.
> >> But I'm sure the table has only 2M. Thanks,
> >>
> >> It is SQL Server 2000. I did it in Query analyzer.
> >>
> >>
> >> The exact queries are:
> >>
> >> select count(ID)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select count(Field1)
> >> from tableA
> >>
> >> select count(field2)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select distinct count(ID)
> >> from tableA
> >>
> >> comment: ID is the identity field.
> >>
> >> I rebuilt/recreated the indexes.
> >>
> >> They all showed as about 12M
> >>
> >> select sum(1) from tableA
> >> I got about 12M
> >>
> >> I know the records in 2M for sure. Also when I did
> >> select *
> >> into temptableA
> >> from tableA
> >>
> >> about 2M rows affected.
> >>
> >> What could be wrong?
> >>
> >> Thanks,
> >
> >
> >.
> >|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>>Hari,
>>sp_spaceused returns me the right number as 2M,
>>but then I did "select count(*) from table_name"
>>That still gives me 11M.
>>Thanks,
>>
>>
>>--Original Message--
>>Hi,
>>Execute the below command :-
>>sp_spaceused <table_name>,@.updateusage='true'
>>THis will return the exact row count. After the
>>
>>successful execution of the
>>
>>above command try executing the
>>select count(*) from table_name
>>Thanks
>>Hari
>>MCDBA
>>
>>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>>
>>When I count records from tableA, I got results as about
>>11M records . But the table actually has only 2M
>>
>>records.
>>
>>I did count(*), or count (ID), or count(distinct ID),
>>
>>all
>>
>>give me the same 12M.
>>But I'm sure the table has only 2M. Thanks,
>>It is SQL Server 2000. I did it in Query analyzer.
>>
>>The exact queries are:
>>select count(ID)
>>from tableA
>>select count(*)
>>from tableA
>>select count(Field1)
>>from tableA
>>select count(field2)
>>from tableA
>>select count(*)
>>from tableA
>>select distinct count(ID)
>>from tableA
>>comment: ID is the identity field.
>>I rebuilt/recreated the indexes.
>>They all showed as about 12M
>>select sum(1) from tableA
>> I got about 12M
>>I know the records in 2M for sure. Also when I did
>>select *
>>into temptableA
>>from tableA
>>about 2M rows affected.
>>What could be wrong?
>>Thanks,
>>
>>.
>>
>
>

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 on a date column

All,
I need to count dates in a column but how can i cut of the time hour minutes and seconds?
I need to rport how many records hav been added on one date...
con someone help me getting on the richt track?
regards
select count(*)
from tbl
where convert(varchar(8),dte,112) = '20040515'
select count(*)
from tbl
where dte >= '20040515' and dte < '20040515'
select dte = convert(varchar(8),dte,112), num = count(*)
from tbl
group by convert(varchar(8),dte,112)
order by convert(varchar(8),dte,112)
one of those should help.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Count of related records?

Hi,

I was wondering if it was possible to build a query that will include a
column that will provide a count related records from another table.
Although there is a way to achieve this through programming in the
front end, I would like to know if it possible to achieve the same
thing through a SQL statement alone.

For example, say you have two related tables, Invoices and
InvoiceItems. InvoiceID is the primary key of Invoices.

Invoices table

InvoiceID PO_Num CompanyID
----------
1 37989 3
2 87302 4
3 78942 3

InvoiceItems table

ItemID InvoiceID PartNo Qty
------------
1 1 ABA 3
2 1 ASLKDJ 2
3 1 9LF 8
4 2 IEPOW 18
5 2 EIWPD 3
6 2 DSSIO 1
7 2 EIWP 5
8 2 DC93 4
9 3 85LS0 8

Then a query that has the Invoices table plus a count of InvoiceItems
for each InvoiceID would generate this:

InvoiceID PO_Num CompanyID ItemCount
---------------
1 37989 3 3
2 87302 4 5
3 78942 3 1

Does anyone have any ideas how this would be done?

Thank you.SELECT I.invoiceid, I.po_num, I.companyid,
COALECSE(T.cnt,0) AS itemcount
FROM Invoices AS I
LEFT JOIN
(SELECT invoiceid, COUNT(*) AS cnt
FROM InvoiceItems
GROUP BY invoiceid) AS T
ON I.invoiceid = T.invoiceid

--
David Portas
SQL Server MVP
--

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 if Conditions Met

I am trying to do a summary SQL query. I have 3 fields. If one filed is
null and the other is not null, I want to count how many records there
are. I also want to count the opposite way then count both fields if
they are both not null. Can I do this within the same query? Help
appreciated.

Thanks,
Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You could use a CASE expression inside SUM/COUNT etc. Here's an example:

CREATE TABLE #x(i int, j int)
INSERT #x SELECT NULL, 1
INSERT #x SELECT 1, 2
INSERT #x SELECT 2, 3
INSERT #x SELECT NULL, 4
INSERT #x SELECT NULL, 5

SELECT SUM(CASE WHEN i IS NULL THEN 0 ELSE j END) FROM #x

Next time, please post your table structures and some sample data, along
with your desired output.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Steve Bishop" <steveb@.viper.com> wrote in message
news:411a5f99$0$14487$c397aba@.news.newsgroups.ws.. .
> I am trying to do a summary SQL query. I have 3 fields. If one filed is
> null and the other is not null, I want to count how many records there
> are. I also want to count the opposite way then count both fields if
> they are both not null. Can I do this within the same query? Help
> appreciated.
> Thanks,
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You could use a CASE expression inside SUM/COUNT etc. Here's an example:

CREATE TABLE #x(i int, j int)
INSERT #x SELECT NULL, 1
INSERT #x SELECT 1, 2
INSERT #x SELECT 2, 3
INSERT #x SELECT NULL, 4
INSERT #x SELECT NULL, 5

SELECT SUM(CASE WHEN i IS NULL THEN 0 ELSE j END) FROM #x

Next time, please post your table structures and some sample data, along
with your desired output.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Steve Bishop" <steveb@.viper.com> wrote in message
news:411a5f99$0$14487$c397aba@.news.newsgroups.ws.. .
> I am trying to do a summary SQL query. I have 3 fields. If one filed is
> null and the other is not null, I want to count how many records there
> are. I also want to count the opposite way then count both fields if
> they are both not null. Can I do this within the same query? Help
> appreciated.
> Thanks,
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 '
"Kalen Delaney" wrote:
> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
> us more info, and maybe even some sample data and sample output, or at least
> an explanation of what you're doing with that value if you want the rest of
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > The following SQL statement returns 38 records:
> >
> > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Rather than retrieving the recordset, I want to simply get a count. My
> > syntax must be wrong (same syntax as above but I added COUNT) because the
> > following query simply returns 38 records with the value "1":
> >
> > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Any suggestions for this beginner? Thanks!
>
>|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that
> this
> query would have returned the value of 2 -- not 3 '
>
> "Kalen Delaney" wrote:
> > Hi
> >
> > GROUP BY always returns 1 row for each different value of the GROUP BY
> > column, so you're still getting one row for each different
> > Base_Contacts.ID_Contact
> > value. If you want the count of all values, don't use GROUP BY.
> >
> > I'm not exactly sure what you want to do with
> > Base_ContactAttributes.ID_Level_Value in the new query. You'll need to
give
> > us more info, and maybe even some sample data and sample output, or at
least
> > an explanation of what you're doing with that value if you want the rest
of
> > the solution.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> > news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > > The following SQL statement returns 38 records:
> > >
> > > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Rather than retrieving the recordset, I want to simply get a count.
My
> > > syntax must be wrong (same syntax as above but I added COUNT) because
the
> > > following query simply returns 38 records with the value "1":
> > >
> > > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Any suggestions for this beginner? Thanks!
> >
> >
> >|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
--
(Please reply only to the newsgroup)

Count from multiple tables

I have 4 tables
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME

My output would be:
username,totalFrom1,totalFrom2,totalFrom3

Thanks For the help!Mybe something like:

select
username,
isnull(t1.ttlfrom1,0) ttlfrom1,
isnull(t2.ttlfrom2,0) ttlfrom2,
isnull(t3.ttlfrom3,0) ttlfrom3
from <users> u
left join
(
select username, count(username) ttlfrom1
from
<t1>
group by username
) t1 on t1.username = u.username
left join
(
select username, count(username) ttlfrom2
from
<t2>
group by username
) t2 on t2.username = u.username
left join
(
select username, count(username) ttlfrom3
from
<t3>
group by username
) t3 on t3.username = u.username

|||Thanks! I would never have figured that one out on my own, but I see how it works.
Thanks again
Greg

Count Changing due to 2 values

why when I leave out the Amount fields, does my query stand correct at 15 records? When I add those 2 amouns (dc.amount and p.amount) back in it for some reason adds 3 more unwanted records and the count is then 18?

SELECT m.customer,
c.name,
c.customer,
c.state,
/*(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule, */
m.Branch,
CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' OR ph.batchtype = 'DUR' OR ph.batchtype = 'DAR' Then
(-ph.totalpaid + ph.ForwardeeFee)
WHEN ph.batchtype = 'PU' OR ph.batchtype = 'PC' OR ph.batchtype = 'PA' OR ph.batchtype = 'DC' OR ph.batchtype = 'DA' Then
(ph.totalpaid + ph.ForwardeeFee)
END AS [Posted Amount],
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
0 AS [New Old CC],
0 AS [New Old PDC],
'In-House' AS Type,
1 AS Active,
ph.UID,
m.number,
dc.amount CC,
--p.amount AS PDC,
m.original,
CONVERT(money, ph.OverPaidAmt),
0,
0,
''
FROM dbo.Master m (NOLOCK) INNER JOIN dbo.payhistory ph ON m.number = ph.number
LEFT JOIN dbo.DebtorCreditCards dc ON dc.number = m.number
LEFT JOIN dbo.pdc p ON p.number = m.number
LEFT JOIN dbo.Customer c ON c.Customer = m.Customer

GROUP BY m.customer,
c.name,
c.customer,
c.state,
c.FeeSchedule,
m.Branch,
ph.OverPaidAmt,
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
ph.UID,
m.number,
--dc.amount,
--p.amount,
m.original ,
ph.systemmonth,
ph.systemyear,
ph.ForwardeeFee

HAVING ph.systemmonth = 12 AND ph.systemyear = 2005
AND m.customer = '0000002'AND m.branch = '00002'

ORDER BY m.customer, batchtype

If you have different values, then this could imply different groups and thus increase the number of rows returned from the query.

Conor