I'm ashamed! I'm stuck on something so simple....
I want to return a value of 1 if count(*)>0 AND 0 if COUNT(*) is 0
I have currently have this below, but isn't there a better way?
SELECT cnt=CASE WHEN (SELECT COUNT(*) FROM MyTable)>0 THEN 1 ELSE 0 END
(The full code is rather more complex than this, but the problem is the same)
Any suggestions welcome.
Cheers!
Mark
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?
View 2 Replies View RelatedHello everybody!
As the topic:
Can i get the value "count(*)" from EXEC('select count(*) from xTable')
Any helps will be usefull! Thanks!
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
How can I tell if table "CompanyInfo" has any rows? Below is the partial code behind and the SP I am running to fill CompanyInfo;
PROCEDURE newdawn.AcceptSubmission @CompanyID intASSELECT C_ID, CG_ID, LS_ID, L_Rank, L_Name, L_URL, FROM tblStoreSubmitWHERE C_ID = @CompanyIDRETURN
SqlCommand cmd3 = new SqlCommand("AcceptSubmission", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd3; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo");
I'm trying to do a sum of a count. I'm running the below query. I want a sum of BrowserCount however I want to return the datatable which is returned by this query. Is that possible should I be using a return value? Or is there another way?
Select UA.Browser_ID, B.Browser_Name_NM, count(B.Browser_Name_NM) as BrowserCount From llc.User_Agent_TB as UA
Left Join llc.Browser_TB as B on UA.Browser_ID = B.Browser_ID
Group by B.Browser_Name_NM, UA.Browser_ID
Order by BrowserCount DESC
This seems pretty straightforward, but I can't seem to figure out how to do this.
So I'm looking at the last fifteen days of data and I want to see how many data points are below a certain value, and the average of those data points. This is the query I'm currently using:
Code Snippet
SELECT COUNT(*) Below, TRUNC(t.THETIME) Day, ROUND(AVG(t.THEVALUE),2) ValAvg
FROM MyTable t
WHERE t.THEVALUE <= 50 AND t.THETIME >= TRUNC(SYSDATE-14)
GROUP BY TRUNC(t.THETIME)
ORDER BY TRUNC(t.THETIME)
I later have to compare the result of this query to the same query without the 't.THEVALUE <= 50' condition. The problem I'm having is that if there are no values below 50 there is no '0' with that date. Is there a way to return 0's for days with no values below 50 that meet the time constraints? Thanks.
Hello. I have a database with the following pivot table which has one record linking each employee in the company to the offices they work at. I'm using a pivot table instead of a direct reference because some employees work at more than one office.
Table Def: tblOfficePivot
-----------------------
key <- PK ID
officeLink <- Link to office record
employeeLink <- link to employee record.
I want to write a select I can use to return the total number of offices with an emplyee population between X and Y (eg. 1-250, 251-500, 501-1000, etc.)
I can write a query which return one result for each office that falls into the range of employees I define. This query looks like this:
SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250
The above query returns 1 record for each office with between 1 and 250 employees - the result being the employee count. So, if 2 offices fell into this category, A & B, having 50 and 123 employees respectively, the result set would look Like this:
theTotal
--------
1. 50
2. 123
What I want instead is the total number of offices, in this case 2.
Is there a way to do this without the COMPUTE clause?
Thanks for any suggestions, this one is driving me crazy.
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?
I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:
The following query:
SELECT t_Category.Name as [Category]
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
Returns this result:
Category
BMS
BMS
Water
BMS
BMS
Air
And the following query:
SELECT COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Category_ID
Returns this result:
AssetQty
4
1
1
I need to have both of those results returned, as a single result. Such as:
Category AssetQty
BMS 4
WATER 1
AIR 1
However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:
Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.
Having a little trouble with this sp... just need to return the count.....CREATE PROCEDURE dbo.getTotalObjectives @courseId VARCHAR(20) ASBEGIN DECLARE @errCode INT
SELECT count(*) FROM objstructure WHERE courseId = @courseId
SET @errCode = 0 RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation FROM cmiDataModel WHERE studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath
I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.
ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04
I need two results.
Total Units = 7 - easy to do by using count()
Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.
Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:----------- ALTER PROCEDURE dbo.CountTEstAS SET NOCOUNT ON DECLARE @sql nvarchar(100); DECLARE @recCount int; DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*) FROM Pool 'SET @parms = '@recCount1 int OUTPUT' exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4 My code that calls this stored proc is: Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") connect.Open() Dim cmd As New SqlCommand("CountTEst", connect) cmd.CommandType = CommandType.StoredProcedure Dim MyCount As Int32 MyCount = cmd.ExecuteScalar connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks,
View 5 Replies View RelatedI'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")
<%response.write rsfind("TotalCount")%>
Thanks for any help!
Dale
i am using the followig query :
select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[code]....
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS (
SELECT PQIM.PQPageID
,PQIM.PageURL as PageDescription
,CONVERT(Date,NCPI.RequestDateTime) AS [Date]
,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR]
,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount
FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
[Code] ....
This is the output I get :
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 04-11-2015 8 2359
1 05-11-2015 8 2332
1 06-11-2015 8 2008
1 07-11-2015 8 1964
1 08-11-2015 8 2139
1 09-11-2015 8 54
[Code] ....
But I am expecting
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 09-11-2015 8 54
1 11-11-2015 9 10
1 11-11-2015 10 4
2 11-11-2015 8 10
2 11-11-2015 9 2
2 11-11-2015 10 1
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
Hi!
I'am new to this forum and would apreciate any feedback on my problem.
I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299.
The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall
FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week,
ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average
FROM SERGEL_PREPAID INNER JOIN
TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE
WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD')
GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP)
HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1
GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help
Best regards Gard S
I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:
Jobs Complete Jobs completed within 5 days
10 5
-------------------------------------------------------------------------------------------------
SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)
---------------------------------------------------------------------------------------
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 need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
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
[code]....
Hi...I have table who contain words in Hebrew (right to left language), and when i try to select:WHERE (Topic LIKE '%Hebrew word%')I get no rows...Any idea? thanks...
View 1 Replies View RelatedIs there any difference in the way Query Analyser will look at a table when you use a 'select * from table' statement compared to using the Open Table, then Return Top from within Enterprise Manager?
We have a 41MB table which contains 50,000 rows. When I try 'select * ...' in QA it seems to run and run (over 5 mins before I have to stop it). One of my users seems to think that using the Open Table, Return Top 50,000 in EM gives pretty immediate results. I'm not so sure however, since if you then attempt to scroll down to the last row it appears to take just as long as the above select statement.
Any ideas, suggestions?
Thanks
Derek
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
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 have sql statement like "select count(*) from table where id = 1", and I want to assign the result to label.text. How do I do that? Thanks.
View 7 Replies View RelatedHello,
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()
End Function
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:
tID | sID ... other fields
----------
1 1
1 2
1 3
1 4
2 5
2 6
2 7
3 8
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 |
Results:
SchoolName
------------
East School