MS SQL Server :: Properties Row Count Not = Select Count(*)

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View Replies !

Mismatch Between Count(*) And Properties -&> Rows
I have a client who has reported a discrepancy in their database. Intheir test database a certain report returns 5,333 rows, but in theproduction database it returns 5, 332 rows.I'll get to the bottom of it in due course, but I came across anoddity. I wanted to know how many rows there were in a particulartable on both databases so I wrote:SELECT COUNT(*) AS NumRows FROM AnswerIt returned 1,919,456. However, if I click on the Answer table inEnterprise Manager and select Properties it tells me that there are1,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 beenno activity (apart from me examining it) for a couple of days.Thoughts?Edward

View Replies !

Table Properties # Rows V.s. Row Count
I've noticed that sometimes the # of rows specified in table properties tab is different from the # shown by select count(*) from tableA.

Why is this ?

(SQL 2k, sp2, indexed table)

View Replies !

Altering Table Issues (total Row Count Vs Exported Row Count)
Please help me...
I had a table with x number of fields...
I had this data source view and model ...
if I selected some information about the table everything was ok...

yesterday I altered the table adding two more fields.
I create a new data source, data source view and model
to get the new database fields included...

now I filter using those fields... and the report is telling me...

81 records... but I exported the data to csv... and I only see 58 records...
I do a manual query thru query analyzer... and got the 81 records...
some records did not appear...

I have no filters... no relations, just one table just one field selected in the report I'm building.

any ideas ???

kindest regards.
elias.

View Replies !

Select Count(*)
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 Replies !

Select Count
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

View Replies !

Select Count
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

View Replies !

Count() And Sub-select
Hello,

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

View Replies !

SELECT COUNT...
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!

View Replies !

Please Help On Select COUNT
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

View Replies !

In SQL 2000 Can I Use Count() To Count A Column?
I use SQL 2000
I have a Column named Bool , the value in this Column is 0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much

View Replies !

Table Row Count + Index Row Count
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View Replies !

DTSDestination.Count And DTSSource.Count
I'm trying to create a VB Script that gets the # of columns from a source table and the # of columns from the destination table and creates a transformation based on the number of columns in the source table.

I can write the actual transformation, but does anyone have an example of how I can get the DTSSource.Count property to return an integer?

Much Thanks,

Dan O'Malley
dan_o@leaseteam.com

View Replies !

Combining 2 Select With Count And Datediff Into 1 Select. Need Help.

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 ')

View Replies !

COUNT ( FROM Nested SELECT)
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

View Replies !

Select Count(*) For Getting # Of Records
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?

View Replies !

Trying To Get A Count In A Select Statement
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.

View Replies !

Select Count Issue
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

View Replies !

Select Rows Where Row Count &> 3
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~

View Replies !

Select And Count From Related MS SQL DB
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.

View Replies !

Get Count From Union Select
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!

View Replies !

Help With COUNT In SELECT Statement
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

View Replies !

SELECT COUNT Of MAX (GROUP BY)
Hello

I want to get the COUNT of

SELECT MAX(id) AS ids, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

doesnt work

thank you

View Replies !

SELECT COUNT Of DISTINCT
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

View Replies !

Count(*) And Select In The Same WITH Statement
Hi,

I have a query:

-- 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 ?

Thanks for help
Przemo

View Replies !

The Awful SELECT COUNT(*)
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

* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
***************************************************************/

AS BEGIN

    DECLARE @nRowCount INT -- the rows
    DECLARE @nObjectID int -- Object ID

    SET @nObjectID = OBJECT_ID(@sTableName)

    -- Object might not be found
    IF @nObjectID is null RETURN NULL

    SELECT TOP 1 @nRowCount = rows
        FROM sysindexes
        WHERE id = @nObjectID AND indid < 2

    RETURN @nRowCount
END
GO

GRANT EXECUTE ON [dbo].[udf_Tbl_RowCOUNT] TO PUBLIC
GO

use Test -- assuming the UDF was created in Test
go

SELECT [name]
     , dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
    FROM sysobjects
    WHERE type='U' and name != 'dtproperties'
    ORDER BY [name]
GO

View Replies !

Help With SELECT Query With COUNT
Here's my tables:

-------------------------------------------------------
tblMembers
-------------------------------------------------------
MemberID | CountryID
-------------------------------------------------------

-------------------------------------------------------
tblCountries
-------------------------------------------------------
CountryID | CountryName
-------------------------------------------------------

-------------------------------------------------------
tblOrders
-------------------------------------------------------
OrderID | MemberID | OrderTypeID
-------------------------------------------------------

-------------------------------------------------------
tblSubscriptionOrders
-------------------------------------------------------
SubscriptionOrderID | OrderID | SubscriptionPackID
-------------------------------------------------------

-------------------------------------------------------
tblSubscriptionPacks
-------------------------------------------------------
SubscriptionPackID | TypeID
-------------------------------------------------------

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.

Can someone help me with this query?

View Replies !

How To Return 1 If Select Count(*) Is &> 0
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

View Replies !

Using A Count W/in A Sub Select Statement
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))

View Replies !

A Question Relating To SELECT Count(*)
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)
{

View Replies !

Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement
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

View Replies !

Select Count One-per-week In Range
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

View Replies !

Select Subquery To Return COUNT
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 Replies !

Blog Select With Comment Count
The following sql works great when the field for my main blog message is type nvarchar but doesn't work for text which I need to convert to.

select
a.id, b.textField
count(b.a_id) as myCount
from a left join b on a.id = b.a_id
group by a.id, b.textField

What other methods could i use to get "myCount" within one sql statement?

Thanks in advance, Jeff

View Replies !

Select Top N Results In A Group; Count(*)&<=n
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?

View Replies !

Ms Sql Select Count Distinct Probrem
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'.

View Replies !

MDX - Select Count Based On Two Different Dimensions
I have a fact table with a create time dimension and an expiration time dimension. I'd like to have a calculated member that would compare the (count for create time) / (count with that expiration time). I already have these counts as measures.

I would be able to put the create time dimension in the "row fields" area, and see the ratio (calculated above)
over the different create time periods.

Can someone point me in the right direction on how I would create that kind of calculated member? What would the MDX look like?

Thanks for your help

View Replies !

Combine 2 Select Count Datediff
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 ')

View Replies !

Count # Of Results From SELECT Statement
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)

Looking to run a query that will retrieve the number of results returned from a select statement...

Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:

SELECT * FROM Software WHERE LicensesID = 2

Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?

Thanks all!

View Replies !

Select With Subfunction Count() Problem MS-SQL
I'm working on an MS-SQL 2005 server, and feel totally lost on fixing a simple problem
of reading count() from the main Table! I think I did the this easely in SQL 2000? Please;

Tables (Some Columns, Collates and constraints removed to save space):

CREATE TABLE [dbo].[Bruker_Detalj]( ---> User info AS BrukerID
[PostID] [int] IDENTITY(1,1) NOT NULL, ---> About 4000 Columns now
[KonsulentID] [varchar](5) NOT NULL,
[BrukerID] [int] NOT NULL,
[Etternavn] [varchar](50) NOT NULL,
[Fornavn] [varchar](50) NOT NULL,
...(several Columns removed)

CREATE TABLE [dbo].[Bruker_Tiltak]( ---> User Action AS BrukerID
[PostID] [int] IDENTITY(1,1) NOT NULL, ---> About 50000 Columns now
[KonsulentID] [varchar](5) NOT NULL,
[BrukerID] [int] NOT NULL,
[StatusID] [int] NOT NULL,
[Status_ar] [int] NOT NULL, ---> Year
[Status_mnd] [int] NOT NULL, ---> Month
...(some Columns removed)

CREATE TABLE [dbo].[Velg_Tiltak]( ---> User Action, Velg = Select
[PostID] [int] IDENTITY(1,1) NOT NULL, ---> About 100 Columns
[KonsulentID] [varchar](5) NOT NULL DEFAULT ('Admin'),
[StatusID] [int] NOT NULL,
[StatusText] [varchar](100) NOT NULL,
[StatusType] [varchar](50) NOT NULL,
...(some Columns removed)

The wanted result is a view with:

Bruker_Tiltak.Status_ar, Velg_Tiltak.StatusType, Velg_Tiltak.StatusText, _
"count(Bruker_Tiltak.(*) AS Status_telle", _
"Bruker_Tiltak.Status_mnd (12 part "pilot" table January-December)", _
(problem: not in code!): _
count(DISTINCT Bruker_Tiltak.BrukerID) for Bruker_Tiltak.Status_ar = Bruker_Tiltak.Status_ar _
AND Bruker_Tiltak.Status_mnd is 1-6 AS FirstHalf, _
count(DISTINCT Bruker_Tiltak.BrukerID) for Bruker_Tiltak.Status_ar = Bruker_Tiltak.Status_ar _
Bruker_TiltakStatus_mnd is 7-12 AS SecondHalf, _
count(DISTINCT Bruker_Tiltak.BrukerID) for Bruker_Tiltak.Status_ar = Bruker_Tiltak.Status_ar _
Bruker_TiltakStatus_mnd is 1-12 AS WholeYear

The "pilot" grouping works fine with a double views:

View Rap_Summering (1):
SELECT TOP (100) PERCENT Bruker_Tiltak.Status_ar, Bruker_Tiltak.Status_mnd,
Velg_Tiltak.StatusType, Velg_Tiltak.StatusText, COUNT(*) AS Status_telle
FROM Bruker_Tiltak INNER JOIN
Velg_Tiltak ON Bruker_Tiltak.StatusID = Velg_Tiltak.StatusID
GROUP BY Bruker_Tiltak.Status_ar, Bruker_Tiltak.Status_mnd, Velg_Tiltak.StatusType, Velg_Tiltak.StatusText
ORDER BY Bruker_Tiltak.Status_ar DESC, Bruker_Tiltak.Status_mnd, Velg_Tiltak.StatusType, Velg_Tiltak.StatusText

View Rap_Sum_Detalj (2):
SELECT TOP (100) PERCENT Status_ar AS Ã…r, StatusType AS Status, StatusText AS Tiltak,
SUM(CASE WHEN Rap_summering.Status_mnd = 1 THEN Rap_summering.Status_telle END) AS Jan,
SUM(CASE WHEN Rap_summering.Status_mnd = 2 THEN Rap_summering.Status_telle END) AS Feb,
SUM(CASE WHEN Rap_summering.Status_mnd = 3 THEN Rap_summering.Status_telle END) AS Mar,
SUM(CASE WHEN Rap_summering.Status_mnd = 4 THEN Rap_summering.Status_telle END) AS Apr,
SUM(CASE WHEN Rap_summering.Status_mnd = 5 THEN Rap_summering.Status_telle END) AS Mai,
SUM(CASE WHEN Rap_summering.Status_mnd = 6 THEN Rap_summering.Status_telle END) AS Jun,
SUM(CASE WHEN Rap_summering.Status_mnd = 7 THEN Rap_summering.Status_telle END) AS Jul,
SUM(CASE WHEN Rap_summering.Status_mnd = 8 THEN Rap_summering.Status_telle END) AS Aug,
SUM(CASE WHEN Rap_summering.Status_mnd = 9 THEN Rap_summering.Status_telle END) AS Sep,
SUM(CASE WHEN Rap_summering.Status_mnd = 10 THEN Rap_summering.Status_telle END) AS Okt,
SUM(CASE WHEN Rap_summering.Status_mnd = 11 THEN Rap_summering.Status_telle END) AS Nov,
SUM(CASE WHEN Rap_summering.Status_mnd = 12 THEN Rap_summering.Status_telle END) AS Des
FROM Rap_summering
GROUP BY Status_ar, StatusType, StatusText
ORDER BY Ã…r, Status, Tiltak
(SQL 2005 server not enabled for PILOT)

Of some reason I am not able to include the COUNT(DISTINCTs x) into the SELECTs.
This is a WEB app. that should work with any (most) SQLs so I should use "plain" SQL for now.
Any tip would be highly appreciated! Writing this may help me to see the solution tomorrow?

Thanks, Per

View Replies !

Obtain Unit Percent With Unit Count Divided By Total Count In Query
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:

--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'

select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc

Thanks. Dan

View Replies !