I have a client who has reported a discrepancy in their database. In
their test database a certain report returns 5,333 rows, but in the
production database it returns 5, 332 rows.
I'll get to the bottom of it in due course, but I came across an
oddity. I wanted to know how many rows there were in a particular
table on both databases so I wrote:
SELECT COUNT(*) AS NumRows FROM Answer
It returned 1,919,456. However, if I click on the Answer table in
Enterprise Manager and select Properties it tells me that there are
1,919,421 rows. I've tried doing a refresh but it didn't work.
The thing is that the database is pretty static - in fact there's been
no activity (apart from me examining it) for a couple of days.
I have a table called Cartype and there is field called typeid contained 5 rows such as A,B,D,C,E,F Ihave another table called transaction that the field called typeid and the data contains 3 rows of A and 3 rows of B , and 1 rows of E I want to write the sql that output something like this
I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.
I want to do the following -
Sum the total no. of rows in mdb while the package executes Sum the total no of rows transferred to Sql server. Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)
Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.
I saw a RowCount transformation..but just dont know where to place and what to do with it.
I have a view that I want to find all the rows that have a matching itemid and have more than 3 rows in them and group them by the itemid. I am not quite sure how to do this. Any ideas? ~mike~
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg
Row A = NoteID 1, FK_JobID 1, UnRead
Row B = NoteID 2, FK_JobID 1, UnRead
Row C = NoteID 3, FK_JobID 1, UnRead
I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.
I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.
Heres my attempt
DECLARE @UserID INT
SET @UserID = 4
SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact, Master_Jobs.Job_Title, Master_Jobs.JobID FROM User_Notes INNER JOIN Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID
WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND BeenRead = 'UnRead'
I'm new to MS SQL having used MySQL for several years now. I just can't figure out the syntax on counting or selecting rows
a) in the Last X (days, weeks, months...)
or
b) between "date X" and "date Y"
I've tried this to get the last week: SELECT COUNT(*) FROM contacts WHERE CONVERT(datetime,timestamp) < (GETDATE() - DATEADD(DAY,7,GETDATE())) ...but I always get a total that includes rows outside the span.
The following query returns 2142 rows which is correct.
Code: select COUNT(*), sum(v.currentMarket) from TRMaster m inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A' where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1 group by m.Year, m.Parcel having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
This is my sql string. It counts all the rows in Questions table but it should only count the rows where id in Quizzes matches the quiz column in Questions table.
"select Quizzes.name, Quizzes.id, count(Questions.quiz) as total from Quizzes inner join Questions on Quizzes.id=Questions.quiz"
I am trying to get the number of rows for each month.
table name:sitez ID Name crDate access =========================== 1 Bob .. 2014-01-11 .. 1 2 Jerry .. 2014-01-22 .. 2 3 Jim .. 2014-05-06 .. 1 4 Jason .. 2014-12-11 .. 1 5 Jen .. 2014-11-21 .. 3
I am using the results to make a bar graph so I am querying the database for a given year and expecting 12 results back (running SQL 2012).
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access = 1 group by all month(crdate)
This should return: 1 0 0 0 1 0 0 0 0 0 0 1
However when testing the script I was only getting back: 1 1 1
which didn't knowing which months were 0
By changing the GROUP BY to GROUP BY ALL, it now puts in the zeroes. However I'm still having issues with incorrect results.
When I query the database, the results for December 2014 shows '13' when I execute:
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access =3 group by all month(crdate)
There are ZERO rows made with a year of 2014 and an access of 3.I verified this by going a straightforward select * from sitez where crdate = 2014 and access = 3
Why I'm seeing ghost results?
All I need is 12 results, ordered by crdate month.
I am doing a SSIS package to transfer rows from access db to sql server using foreach file. Before loading the data, I want to count the number of rows in a table of the access db. How do I do that?
i have a table like thisStoreId DateClicked1 1/1/20042 1/1/20041 1/1/20041 1/1/20041 1/1/20043 1/1/20042 1/1/2004I want the result of the query to be this:NumberOfClicks StoreId4 12 21 3what is the syntax for this? Thanks.
I built a report with one field as a group. I want to count the number of rows in each field so I can add it to the group field or somw where in the report.
How can I count how many rows do I have in each group?
Hi everyone: I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting? SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2 This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all. Thanks for any help.
My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned. Does this also work when calling stored procedures and passing parameters? I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1. What should I do?Dim myCommand As New SqlClient.SqlCommand myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime" myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.AddWithValue("@selectDate", dateEntered) myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry)) myCommand.Connection = con con.Open() Dim rowCount As Integer = myCommand.ExecuteNonQuery() numberParts.Text = rowCount.ToString con.Close() Thank you.
hello, i have a stored procedure SELECT CommentID, UserName, CommentingDate FROM Comm WHERE PictureID = @PictureID ORDER BY CommentingDate DESC witch shows me the users who commented a Picture with PictureID = x I need to add two rows at that stored procedure, one to show the number of total comments at that picutre (like counting the number of rows returned) and the second to show count the DISTINCT users who commented that picture I tryied with COUNT but i have to use GROUP BY and i don't think this is good... I hope you understand... please help me, thanks
I would like to know the best way to count and display the number of rows in a given database table called memberinfo. This should tell me how many members I have right? Thanks
This is my SQL : Select p.patientid,p.patientname,p.patientIc,pvi.DateOfAdmission,pvi.visitid,pvi.ward,pvi.bedno,pf.status,pvi.SurgeonName,(f.Title + ' ( Ver ' + (CAST(f.Version as Char(10))) + ')') as Title FROM patient p, patientvarianceinfo pvi,patientForm pf,Form f where (p.PatientName LIKE '%" & Name & "%' or p.PatientIc LIKE '%" & ic & "%' or pvi.Ward LIKE '%" & ward & "%' or pvi.Bedno LIKE '%" & bed & "%') and (p.patientid = pvi.patientid) and (p.patientid = pf.patientid) and (pvi.patientid = pf.patientid) and (pf.FormID = f.FormID)and p.patientid in (select patientid from patientform pf)how do i get the number of rows?
I want to have a select that will pull the following information out with count = the number of rows that have matching data in data4
ID data1 count 1 535 2 2 325 2 3 654 1 4 765 1
Right now I am using a VB script to loop thru get the current data4 value then using SELECT COUNT(data1) AS count FROM tbl_toolerrors WHERE data4 = {data4 value currently looking at}
Of course this take a bunch of trips to database and I think there should be a way to do it. I was thinking of a nested SQL querry like
Select data1, data2, ID, data3, (select count ...) Order by data1
I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.
I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...
So far I have this:
INSERT INTO newtable COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName FROM Raw_Table GROUP BY Raw_Table.FieldName);
I have a problem that I can't quite get started on solving.I have a table of asset statuses. Each time the asset status changes, anew row is inserted into the table.CREATE TABLE dbo.Tbl_EMStatusHistory (EMStatusID int IDENTITY (1, 1) NOT NULL ,EMSessionID int NULL ,AssetID int NOT NULL ,AssetStatus int NOT NULL ,StatusComment varchar (250) NULL ,StatusDate smalldatetime NOT NULL ,InsertUser sysname NOT NULL ,InsertDate smalldatetime NULL ,CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED(EMStatusID))Here is some sample data:EMStatusID AssetID AssetStatus StatusDate DeviceType4772622624OUT2003-10-05Monitor3810022624IN2003-10-16Monitor4726122624OUT2003-10-25 Monitor3819322624IN2003-11-02 Monitor3917122624RV2004-05-02 MonitorFor asset 22624, the current (most recent) status = RV. Before that, itlastchanged status on 2003-11-02, when it was IN. So it was IN from2003-11-02 to2004-05-02.I need to produce a report that counts the devices by type that were INon a given report date,like so:ReportDateMonitorsTransmitters05/01/2004342605/02/2004373005/03/2004393205/04/20043531The end user supplies the date range.I am unsure how to approach this. I came up with oneEinsteinian-Newtonian method that involved three temp tables, a gardenhose, and a duck, and it was getting uglier by the minute. Has anyonehad experience with a requirement like this? Thanks, Dave"Are you thinking what I'm thinking, Pinky?"*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
