Counting Records Using A WHILE Cycle

May 27, 2015

I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:

Code:
WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c
BEGIN
SELECT COUNT(DepartmentID) AS DepartmentCount
FROM dbo.tblTask
WHERE (DepartmentID = @Variable)
END

how could I build this query or what better way of doing the job there are...

View 1 Replies


ADVERTISEMENT

Counting Records

Aug 18, 2006

Hello all.

I am looking to select all from a particular row where one field occurs more than once. The code i'm, trying isnt working:



select * from U_DATAFILE
where count(group by VIEW_ACTION)>1



Thanks all :)

View 10 Replies View Related

Counting Records

May 29, 2007

I have a table with three fileds
1 - SmsTime (datatype = datetime)
2 - MsgCnt (datatype = int)
3 - Message (datatype = varchar)

Here MsgCnt means it will give the number of sms part (70 charector is equal to One SMS).
So in each row MsgCnt may be vary. Some time it may be 1 or 3 or 5 or 2 etc.
Now here I need to count how many MsgCnt is received on Today. Its not to count the rows but counting the MsgCnt fild value.
Is there any sql way to find it.

Plesase help me!

View 8 Replies View Related

Counting All Records In All Tables

Feb 26, 2001

I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?

View 1 Replies View Related

Counting Records In A View

Sep 14, 2006

I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.

Is this possible using a view?

View 5 Replies View Related

Problem Counting Records

Nov 30, 2007

Hi,I am struggling with a simple query, but I just don't see it.I have the following example table.Table MessagesID Subject Reply_to1 A 02 Ax 13 A 14 B 05 By 46 C 0The table holds new messages as well as replies to messages.Messages with Reply_to = 0 are top messages, the other messages arereplies to a top message. The subject of a reply message does notnecessarily have to be the same as the subject of the top message.What I would like to have returned is this: a list of messages whereReply_to = 0 and the number of replies to this message.ID Subject Num_replies_to1 A 24 B 16 C 0Any assistance would be greatly appreciated.

View 3 Replies View Related

Fast Counting Of Records

Jul 20, 2005

I seem to remember reading many moons ago about a function where youcan retrieve a count of the last recordset you opened.For example:I've got a stored procedure that returns a recordset using the TOP 10so I only get the top 10 records. I need to know the recordcount but Idont want to reuse the SELECT statement because its quite complex.Any ideas?What does @@Count do?Thanks in advance

View 1 Replies View Related

Counting All Records In A Database

Jun 9, 2007

Is there an easy way to count all records in a database?

View 3 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Counting Records In A Stored Procedure

Jul 14, 2004

I am trying to count records in my stored procedure. Can someone please help me.

these are the two procedures I am using

Alter Procedure usp_rptQualityReport As

SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber

WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))

ORDER BY
tblFailureReasons.FailureReason



Alter Procedure usp_rptQualityReport2 As

exec usp_rtpQualityReport

SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,

(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel


FROM usp_rptQualityReport;

View 3 Replies View Related

Counting Parent Records And Displaying Total??

Feb 5, 2004

We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:

parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3

This is what I would like to have:

parameter # Calls
------------ -------
desktop 15
network 19
software 3

Please Help!

View 2 Replies View Related

Counting Records In All Non System Database Table

Jun 9, 2007

is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?

View 4 Replies View Related

Counting Distinct Records Of Column 1 With A Certain Value In Column 2

Feb 6, 2008

Ok, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing


dbo_dbWafer_Slicing



WaferID
SawDate
SawRunNumber
   Pass





03-157.05    
1/8/2008 9:54:00 AM    
03-157
0




03-157.03
1/8/2008 9:53:00 AM    
03-157
-1




03-157.04
1/8/2008 9:53:00 AM    
03-157
0




03-157.02
1/8/2008 9:52:00 AM    
03-157
-1




03-157.01
1/8/2008 9:50:00 AM    
03-157
-1




03-165.06
1/4/2008 10:46:00 AM    
03-165
0




03-165.07
1/4/2008 10:46:00 AM    
03-165
0




03-165.04
1/4/2008 10:45:00 AM    
03-165
0




03-165.05
1/4/2008 10:45:00 AM    
03-165
0




03-165.02
1/4/2008 10:44:00 AM    
03-165
0




03-165.03
1/4/2008 10:44:00 AM    
03-165
0




03-165.01
1/4/2008 10:43:00 AM    
03-165
0





 So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help. 

View 6 Replies View Related

Error Log Cycle

Mar 10, 2005

Quick question (I hope) regarding the cycling of the sql server error logs.

I am looking at implementing a daily / weekly job to run the stored procedure "sp_cycle_errorlog", but was wondering if there was any way of stopping the sql server process cycling the logs automatically when it is started? Therefore the logs will only be cycled when the job runs.

Many thanks.

View 1 Replies View Related

How Can You Stop And Cycle Through A Column(s) Of

Jun 16, 2007

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

View 2 Replies View Related

52 Week Backup Cycle...

Sep 13, 2007

Hi

uh

im on the blag....

Can someone help me with a script for the following?

7am tuesday, week 1 of the year:
full backup, Trans log backups from between 8am and 10pm and nightly diff @ 10.15pm. both TL backups and Diff backups append to the full file. the next day - weds - exactly the same only without the initialising full backup @ 7am. each day appends to the same weeks backup. this goes all the way through to 10.15pm Monday night which is the last activity on the particular backup file.

7am Tuesday, week 2 of the year:
exactly the same process but writing to a new backup file. backup file is named something useful, like with the date of the Tuesday for example..

ill manually delete things as I need to (once that months data has been written to End-Of-Month tape), but otherwise i'd ideally end up with 52 backup files, each containing a weeks worth of data.

help me obi-wan SQLee. cos I cant bloody do it....

Thanks

Alastair
Methodology



"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

View 6 Replies View Related

How Can You Stop And Cycle Through A Column(s) Of Data?

Jun 16, 2007

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

View 3 Replies View Related

SQLAgent Doesn't Cycle Log Gracefully

Jan 26, 2007

Scenario: SQL Agent job calls "EXEC msdb.dbo.sp_cycle_agent_errorlog" once a week to cycle the SQL Server Agent log. Job is owned by "sa".

Most servers run this with no problem, but one active node of an active/active SQL Servers cluster fails with the message:

Executed as user: DOMAINSQL_Service. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). The step failed.

The SQLAgent job actually appears to be doing its job... a new SQLAgent.OUT is generated with the event:

(Date/Time)+ [412] Errorlog has been reinitialized. See previous log for older entries.

If I try renaming the file SQLAGENT.OUT, I get the message "It is being used by another person or program," which I expect. If I stop the SQLAgent service, I can rename the file with no problem. Failing over has no effect.

I just don't understand why this job fails for this server. (It should be mentioned the job could be fixed to "Succeed on Failure," but I'd rather not.)

View 14 Replies View Related

Help Problem On SELECT Cycle Backward

May 29, 2008

need help on logical SELECT cycle



Code Snippet
SELECT empID, unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, unit_date, GETDATE())) % 4, 0), 4) AS new_unit
FROM dbo.empList




i have this code
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
IT OK

but

if i put
unit_date = 01/04/2008
and unit=1
than i get new_unit=2

but it should be 3
it dont go backward ok



if i put
unit_date = 01/06/2008
and unit=1
than i get new_unit=4

but it should be 2
it dont go forward ok !

TNX

View 1 Replies View Related

Query To Show Life Cycle Revenue?

Jan 2, 2007

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table  and a sales table joined by custno.
SELECT     Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM         Customer INNER JOIN                      Sales ON Customer.CustNo = Sales.CustNo
 The output I'd like is
CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount
Getting the first inv date seems straight forward
SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo
However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.
 Can anyone point me in the right direction?
 

View 2 Replies View Related

Display Flag For OrderType Cycle Values

Aug 9, 2015

I have a table with EquipmentNumber, OrderType, BreakdownIndicator and RowID columns and below are the values for one equipmentnumber,

EuipmentNumber OrderType BreakdownIndicator RowId Flag
145812 PM04 1 Yes
145812 PM07 2 Yes
145812 PM07 3 Yes
145812 PM04 4 Yes
145812 PM07 5
145812 PM07 X 6
  145812 PM07 7
  145812 PM04 8 Yes
145812 PM04 9 Yes
145812 PM04 10 Yes

I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,

1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.

View 12 Replies View Related

Transact SQL :: Get Start And End Date Of Billing Cycle

Oct 9, 2015

I have weekly billing cycle. And the billing cycle can start on any day of the week.

Assumption: Start day of week is MONDAY

Hence, the following

DayNumber Day
1 Monday
if start day is 1, then billing cycle is Monday - Sunday
2 Tuesday
if start day is 2, then billing cycle is Tuesday - Monday
3 Wednesday
if start day is 3, then billing cycle is Wednesday - Tuesday
4 Thursday
if start day is 4, then billing cycle is Thursday - Wednesday
5 Friday
if start day is 5, then billing cycle is Friday - Thursday
6 Saturday
if start day is 6, then billing cycle is Saturday - Friday
7 Sunday
if start day is 7, then billing cycle is Sunday - Saturday

For a given date, i need find start date and end date of current billing cycle.

For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015

View 6 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

IDC And Counting

Jun 26, 1998

When I have made a query with IDC, how can I make a counter in the .htx-file, which tells me if there was no query-results ?

View 2 Replies View Related

A Q About Counting

Jan 28, 2007

Hi,New to SQL. Got some questions about it.Suppose I have two tables. Each of them has a single column, named asc1. For table T1, I have:11133579For table T2, I have:1234513The exercise I want to do is to select the number of occurence in T1for those elements in T2. For above tables, I want to show:1 3 ( i.e. "1" is in T2 and shows 3 times in T1)2 0 (i.e. "2" is in T2 but doesn't show in T1)3 2 (i.e. "3" is in T2 and show 2 times in T1)It seems I can't figure out a good way to do this. Any help will beappreciated.Thanks

View 2 Replies View Related

Counting In SQL

Oct 24, 2007



I am working on writing a query to use with Reporting Services where I am pulling grades from a school grade book trying to see what students qualify for Honor Roll.
I have one Honor Roll done but am still working on the other. The qualifications I am trying to get is that a student has a gpa >3.0 and can have one C grade. I've got the 3.0 and the C's down but how to get the query to pull anyone having at the most one C. It doesn't matter whether it is C+, C, or C- but the student can't have more than a count of one.

Here is what I have


SELECT DISTINCT s.lastname+','+' '+s.firstname AS Student, s.studentnumber,e.grade,t.name AS Term,

gs.score, c.name AS Course, se.teacherdisplay

FROM GradingScore gs

INNER JOIN student s ON s.personid = gs.personid

INNER JOIN v_TermGpa tg ON tg.personid = s.personid

INNER JOIN enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid = gs.calendarid

INNER JOIN scheduleStructure ss ON ss.calendarid = gs.calendarid

INNER JOIN Termschedule ts ON ts.structureid = ss.structureid

INNER JOIN Term t ON t.termscheduleid = ts.termscheduleid

INNER JOIN section se ON se.sectionid = gs.sectionid

INNER JOIN course c ON c.courseid = se.courseid

WHERE gs.calendarID = 20 AND t.name ='1st 6wk' AND tg.term1gpa >=3.0 AND s.enddate IS NULL

AND gs.score <='C-'

AND (select gs1.personid, gs1.score

from gradingscore gs1

inner join enrollment e1 on e1.personid = gs1.personid

Inner join schedulestructure ss1 on ss1.calendarid = e1.calendarid

Inner join termschedule ts1 on ts1.structureid = ss1.structureid

Inner join term t1 on t1.termscheduleid = ts1.termscheduleid

where gs1.score IN('C','C+','C-') and (count(score)= 0 or count(score) < 2))

ORDER BY s.[student]

I can get it to work all the way up to where the And (count(score) starts then there is something about the way the count is in there that I haven't figured out yet.

Thanks in Advance for any help or advice

View 9 Replies View Related

Counting The Sum Of Each Row?

Jan 26, 2008



Hi,

How do I count the total number of each row in a table? The table Company has a field called Group. How do I count the total amount of members in each group?

Thanks in advance!

View 23 Replies View Related

Auto Counting In SQL

Oct 5, 2007

Hi all,
I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.
So if I for example select all Dates older than today's date, I would want something like this:




1
10/12/2006

2
10/18/2006

3
10/20/2006

4
10/22/2006

5
10/30/2006
Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".
Is this possible? And if yes, how ? :)
 
Thanks in advance
Nick

View 6 Replies View Related

I'm Stuck With COUNTING

Jan 31, 2008

Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing

View 2 Replies View Related

Counting Problem

Mar 15, 2004

Hi everyone,

another problem:

I'm trying to count the number of rows but it's not working. Here's my code:


SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
(
SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
FROM [Activites]
)


As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.

Any help?
Andrew

View 3 Replies View Related

Counting Items

Jun 22, 2004

Hi,

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

Cheers.

View 6 Replies View Related

Counting Rows

Oct 19, 2005

Is there a way to get a SQL Stored Procedure to count the number of rows returned and then store that total in a variable?

View 1 Replies View Related

Aggregate Counting

Aug 17, 2006

Hi,

How can I aggregate a top 5 count across two satellite tables?

e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.

Any help or pointers would be a great help!

Thanks.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved