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.
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?
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.
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...
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.
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
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.
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
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?
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.
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.
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
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.
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?
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 ? :)
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
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.
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?
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.
is there any way to set up a column that has the row count in it? i need this for a program i am developing and this would make it much easier to deal with. I know i can get a total count but when i run a count within a select statement i just get '1' for every row. thanks
Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:
SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55 AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9
This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.
now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!
hiya all, I got a table that is represented as a B-Tree, it has a one to many relation with its self ------------ | UserID | ------------ |FatherID| ------------
UserID = FatherID it looks like this o /// \ ooo ooo //\ ///\ oo ooo ooo I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it. any ideas ? the DB isn't filled with data yet so we can add more fields to the table if it helps.
I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.
Hi all, I am trying to count the total amount of times the primary key is used within each month of the year "JAN", "FEB" "MAR"... Unfortunately I have no idea how to A) Search by month and B) Count the total amount of times data within the new table that is created AFTER the outer joins are done. I hope you can help.
Thanks.
SELECT * FROM tableA ta, tableB tb, tableC tc WHERE tc.updated_date >= TO_DATE ('01/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS') AND tc.updated_date <= TO_DATE ('31/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.primaryKey = tb.primaryKey (+) AND tc.primaryKey = ta.primaryKey (+) AND count(tc.priarykey) -- This is what I want to count ;
select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true) FROM Car c LEFT JOIN Trailer t on t.CarId = c.CarId GROUP BY c.CarId, c.CarName, c.CarColor
