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