Hello,
I would like to count the number of items in a table. I used the following code:1 Dim Comments As Integer
2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
3 Dim SqlCommand As New SqlCommand("SELECT COUNT(CommentID) FROM Comments WHERE ThemeID = '3', cnn")
4
5 cnn.Open()
6 Comments = SqlCommand.ExecuteScalar()
7 cnn.Close()
But this only gives me the error message "ExecuteScalar: Connection property has not been initialized."
Can anyone help me with this?
Thanks
Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed' From Project WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
I need to select total number of rows from my data base table....here is what ive been trying....I know it wrong...but maybe someone can fix it. Thank you very much. Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As BooleanDim MyConn As New Data.SqlClient.SqlConnection(ConnectionString) Dim cmd As New Data.SqlClient.SqlCommand("Select count * from ClassifiedAds", MyConn)Dim dr As Data.SqlClient.SqlDataReader 'cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@UserName", textbox_username.Text)) ' cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@Password", textbox_password.Text)) cmd.Connection.Open() dr = cmd.ExecuteReader() dr.Read() If dr.HasRows Then Label_totalclassifieds.Text = dr.Read Return True Else dr.Close() cmd.Connection.Close() Return False End If End Function
I'm new to SQL. For a statistic application, I wish know the subtotal of lines pro region (Mitte, ost, west, ost, etc).
How can I do that?
A lot of thx for your help and time, Regards, Dominique
Code:
SELECT distinct case when ANZSUCHEN.KANTON = '' then 'nicht_zugeteilt' when ANZSUCHEN.KANTON = '----------------------------------' then 'nicht_zugeteilt' when ANZSUCHEN.KANTON = 'AG' then 'mitte' when ANZSUCHEN.KANTON = 'AI' then 'ost' when ANZSUCHEN.KANTON = 'AR' then 'ost' when ANZSUCHEN.KANTON = 'BE' then 'bern' when ANZSUCHEN.KANTON = 'BL' then 'mitte' when ANZSUCHEN.KANTON = 'BS' then 'mitte' when ANZSUCHEN.KANTON = 'FR' then 'west' when ANZSUCHEN.KANTON = 'GE' then 'west' when ANZSUCHEN.KANTON = 'GL' then 'ost' when ANZSUCHEN.KANTON = 'GR' then 'ost' when ANZSUCHEN.KANTON = 'JU' then 'west' when ANZSUCHEN.KANTON = 'LU' then 'mitte' when ANZSUCHEN.KANTON = 'NE' then 'west' when ANZSUCHEN.KANTON = 'NW' then 'mitte' when ANZSUCHEN.KANTON = 'OW' then 'mitte' when ANZSUCHEN.KANTON = 'SG' then 'ost' when ANZSUCHEN.KANTON = 'SH' then 'ost' when ANZSUCHEN.KANTON = 'SO' then 'mitte' when ANZSUCHEN.KANTON = 'SZ' then 'mitte' when ANZSUCHEN.KANTON = 'TG' then 'ost' when ANZSUCHEN.KANTON = 'TI' then 'west' when ANZSUCHEN.KANTON = 'UR' then 'mitte' when ANZSUCHEN.KANTON = 'VD' then 'west' when ANZSUCHEN.KANTON = 'VS' then 'west' when ANZSUCHEN.KANTON = 'ZG' then 'mitte' when ANZSUCHEN.KANTON = 'ZH' then 'ost' end as region, (SELECT count(*) FROM ANZSUCHEN WHERE ANZSUCHEN.KANTON = 'FR') FROM ANZSUCHEN GROUP BY ANZSUCHEN.KANTON ORDER BY region
Results:
Code:
region (No colomn name) bern34 mitte34 nicht_zugeteilt34 ost34 west34
I have the following code in VB.Net trying to count # of records in the datatable. Upon execution of the code, it returns only 1 record. I know for fact that there are more than 1 record in the datatable (there are 230 records in the table).
Can somone please tell me where is my problem ? Public Function Get_Record_Count(ByVal Table_Name As String, ByVal Criteria As String) As Long Dim tbl As DataTable
strSQL = "SELECT count(*) FROM [" & Table_Name & "]" If Len(Criteria) > 0 Then strSQL &= " WHERE " & Criteria End If
ADO_Adapter = New OleDb.OleDbDataAdapter() tbl = New DataTable(Table_Name) ADO_Adapter.SelectCommand = New OleDbCommand(strSQL, ADO_Conn)
Dim Builder As OleDbCommandBuilder = New OleDbCommandBuilder(ADO_Adapter)
ADO_Adapter.Fill(tbl) 'Return # of records found in table Get_Record_Count = tbl.Rows.Count.ToString tbl.Dispose()
Hallo! I have a table student(teacherID, studentID, studentName...) that contains data for a certain student and a table teacher(teacherID, teacherName...) that contains data for the teacher. Student can be imagine as follow:
For example, teacher of tID=1 teachs to 4 students. I'd like to select all the fields from teacher where the teacher has more than x students. Is it possible? How can I do? Thank you!
I am having trouble creating a query which will list schools with ALL their Admission = Null. For example, if at least one record is not null, don't list it, but as long as ALL the records have Admission = Null, then list it. I hope that's clear. Thanks!
SchoolTable:
SchoolName | ApplicantName | Admission ------------------------------------------------ North School | Student1 | Admitted North School | Student2 | North School | Student3 | East School | Student4 | East School | Student5 | East School | Student6 | West School | Student7 | Admitted West School | Student8 |
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~
Hello,We have a database filled with local restaurants for a directory. What I'm trying to do is display a list of all the cuisines and the number of restaurants who serve that type of cuisine in parentheses. It should look like the example below:American (12)Chinese (3)Italian (5)...Here are the tables with only the relevant columns: restaurants---------------...cuisine_id... cuisines-----------cuisine_idcuisine_name... So basically I want to pull all the names from the cuisines table and then the restaurant count from the restaurants table. The only way I can think of doing that is to grab the cuisine_ids, put them in an array and then use that in a loop to get the count from the restaurants table. That's not very elegant. Is there a better way?Thank you
When I try and execute this query I get the belwo error. I want to get the ItemName and the Count as one column. How can this be done? SELECT itemName, itemName +' - '+ COUNT(itemName) AS itemNameCount FROM tblItems GROUP BY itemName ERROR: Conversion failed when converting the nvarchar value 'Spark Plug - ' to data type int.
Hello, I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code: Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString) Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader Con.Open() reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1")) reader.Close() Con.Close()
I am getting an error that that's no data in the table. Any suggestions?
HI All, I have what is most likely a simple MS SQL query problem (2005).I need to add two computed columns to a result set, both of those columns are required to provide the count of a query that contains a parmamter. (The id of a row in the result set) 3 Tables (Showing only the keys)t_Sessions-> SessionID (Unique) t_SessionActivity-> SessionID (*)-> ErrorID (Unique) t_SessionErrors-> ErrorID (1) I need to return something like (Be warned the following is garbage, hopefully you can decifer my ramblings and suggest how this can actualy be done) SELECT SessionID, (SELECT COUNT(1) AS "Activities" FROM t_SessionActivity WHERE t_SessionActivity.SessionID = t_Sessions.SessionID), (SELECT COUNT(1) AS "Errors" FROM dbo.t_Sessions INNER JOIN dbo.t_SessionActivity ON dbo.t_Sessions.SessionID = dbo.t_SessionActivity.SessionID INNER JOIN dbo.t_SessionErrors ON dbo.t_SessionActivity.ErrorID = dbo.t_SessionErrors.ErrorID WHERE t_SessionActivity.SessionID = t_Sessions.SessionID)FROM t_Sessions Any help greatfully received. Thanks
Plz help me out making a SELECT for my imagegallery. Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all. Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId} In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath] To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle] To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID() -But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
helloi want to do only one query for :SELECT DISTINCT Name FROM UsersSELECT COUNT(Name) AS Names FROM Users WHERE (Name LIKE 'xxx')something like :SELECT Name, COUNT(Name) AS Names FROM Users WHERE Name IN (SELECT DISTINCT Name FROM Users)i must get :Joe 23julie 17.....thank you
Here's what I'm trying to do: 1. Output each country in one column 2. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 1 in the next column 3. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 2 in the next column
My problem was that I was doing joins, and I was somehow ending up with orders where the OrderTypeID was NOT equal to 3, even though I declared it specifically in the WHERE clause.
I have two tables. Houses and Guests. The guests are the people who visited the houses tied by the HouseNo.
I want to list all of the houses which has HouseName, Address, HouseNo. Then I want to show a column for the number of guests who visited it.
The Guest table has GuestName and HouseNo. How can I do a select on all houses and then show a column of COUNT(GuestName) for all the guests with the HouseNo matching each row?
I'm trying to create a DTS package that uses CDO to send users an email. I need to create a sql query that counts two columns. I also need to create aliases for these two columns and then reference this in the sendEmail function. I have something that looks like this but I'm getting a DTS error. I think that it's because I'm not using an alias to reference Valid and Invalid. Can someone tell me how to alias the subselect columns correctly?? thanks :)
select advertiseremail, accountnumber from miamiherald where AdvertiserEmail is not null (select Valid = (select count (*) from miamiherald where validad = 1), Invalid = (select count (*) as Invalid from miamiherald where validad = 0))
Hi,I'm trying to get the count of rows from the union of several tables.My code is:select count(*) from (select * from #AdvSearch_Mainunionselect * from #AdvSearch_Atty)This will not get past the syntax check saying that the error occurs onthe final closing ")".Can someone tell me how to correctly write this?Thanks,Glen--------------------------Numbers 6:24-26----------------------------------------------------Numbers 6:24-26--------------------------*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Could someone assist with getting the count function working correctlyin this example please. I know the count function will return all rowsthat do not have null values, but in this case I want to count all therows except those with a zero sale price, (which are unsold).The table shows works offered for sale by an artist, with a positivefigure under SalePrice indicating a sale, and I want to count thenumber sold by each auction house, and sum the sale price by auctionhouse. The table is as follows:NameSalePriceAuctionDowling12000ChristiesDowling 0ChristiesDowling10000ChristiesDowling 0ChristiesDowling 0ChristiesDowling 6000SothebysDowling 0SothebysDowling 0SothebysDowling 8000SothebysDowling 0SothebysDowling 0SothebysDowling 0SothebysWhen I run this query:SELECT MyTable.Name, Count(MyTable.Name) AS [Number],Sum(MyTable.SalePrice) AS TotalSales, MyTable.AuctionFROM MyTableGROUP BY MyTable.Name, MyTable.AuctionHAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));The results are:NameNumberTotalSalesAuctionDowling 5 22000 ChristiesDowling 7 14000 SothebysThe TotalSales is correct, but the Number (Count) is incorrect, as therows with zero were also included. The results should be:NameNumberTotalSalesAuctionDowling 2 22000 ChristiesDowling 2 14000 SothebysHow do I prevent the unsolds (zeros) being counted?Thanks in advance,John Furphy
We have one table have problem when run "select count(*) from table". This is a sql 2000 server. it returned different count everytime when I ran the "select count(*) from table". I used DBCC to check the table and did not find any problems. I can fix it by recreate the table and reimport the data, but the problem come back after we update some data in the table monthly with a stored procedure. Any suggestion?
I found some really helpful code that allows for quick retrieval of select count(*). But am I right in saying that select count(*) can cause locks on the table?
This is the code dbcc updateusage (N'Test') go select count(1) from Testdb go CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (
@sTableName sysname -- Table to retrieve Row Count )
RETURNS INT -- Row count of the table, NULL if not found.
/* * Returns the row count for a table by examining sysindexes. * This function must be run in the same database as the table. * * Common Usage: SELECT dbo.udf_Tbl_RowCOUNT ('')
* Test PRINT 'Test 1 Bad table ' + CASE WHEN SELECT dbo.udf_Tbl_RowCOUNT ('foobar') is NULL THEN 'Worked' ELSE 'Error' END
-- main select WITH Orders AS ( SELECT ROW_Number() OVER(MyDate ASC) RowNo, ** rest o the query *** ) SELECT * FROM Orders WHERE RowNo BETWEEN 100 AND 200 ORDER BY RowNo
--count of records DECLARE @COUNT INT SELECT @COUNT = COUNT(*) FROM ** the same query as above *** RETURN @COUNT
In this case it can happen that when counting records there will be different number of records that it was at time of paging. Also server has to execute this query twice and the query is quite complicated means that takes time.
Is there any better way to get number of rows in the same part of query with paging ?
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
Hi,I'm apologizing in advance for using someone else's brainpower on what is basically a logic problem rather than a creative use of T-SQL, but it's doing me head in.I have a table which contains information on bookings for runs of advertising. This includes a BookingStart dateTime field and a BookingEnd datetime field. Between BookingStart and BookingEnd an ad is considered to be "live".What I need to do is construct a query which, given a start date and and end date returns a count of all the records which were "live" between those dates and - here's the catch - each week must count separately. In other words if my query asks for data across four weeks, and an ad was "live" for two of those weeks, it should return a count of two.Any help much appreciated.Cheers,Matt
I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN SELECT Code, COUNT(Code)as exprCount1a FROM dbo.[Test] WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code END The results of the statement: Code | exprCount1a 1 22 44 1 I would like the following results: Code | exprCount1a 1 22 4 3 04 1 Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results. Thank you in advance
I am attempting to determine if a record for TODAY has been written. If one, I update it. If no records for TODAY have been created yet, I INSERT a new one. I am basing my "if" (C#) on a Select count(*) WHERE as shown below. My problem is that I do not know how to obtain the result. The debugger displays a -1 as the count and the IF falls through to its companion ELSE. Can anyone please set me straight? Oh, I build 'TestDate' from getdate and it ends up as a str like "6/10/2008". The Date column is also a varchar with the same kind of string as its contents.String fpsqlA = ("SELECT count(*) from DailyCounts WHERE Date = '"+testDate+ "'"); SqlCommand fpcmdA = new SqlCommand(fpsqlA, connStringDaily);fpcmdA.CommandType = CommandType.Text; int iCount = fpcmdA.ExecuteNonQuery();SqlDataAdapter ad1 = new SqlDataAdapter(fpcmdA); ad1.Fill(dtA);
//Test to see if a record for 'today' has already been written to the DailyCounts table. if (iCount <= 0) {
Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region) ProductID is the primary key.
I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.
I tried the follwoing, but get incorrect results.
select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price
However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.
select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price
I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?
I am trying to get the below query to work and can't seem to get past this error. Not sure but everywhere I look I think my syntax is right. I am probably missing something obvious but I'm just back from vacation and trying to get in the swing of things. Any help would be appreciated.
Select Distinct(chadcd) as Adjustor, Count (Distinct chclno) as NumberOfCases, Count (Distinct chclno,chwkno)as NumberofClauses from clmhdr where chpddt = '20040630' Group By chadcd Order by Adjustor
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ',chwkno'.
