Query Returns Inaccurate Count

Dec 13, 2014

Why does this return 64.00000 Hours Worked when if you do the calculation yourself you see that it should only be 16?

Code:
Create Table #One
(
BadgeNum int,
NameOnFile varchar(1000),
hoursworked int

[code]....

View 3 Replies


ADVERTISEMENT

Nested SELECT Query That Also Returns COUNT From Related Table

Mar 4, 2005

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!

View 3 Replies View Related

Group By - Count Returns No Rows

Jun 12, 2007

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.

View 4 Replies View Related

Inaccurate Data For Tempdb

Sep 23, 1999

Presently, we have allocated 600MB to tempdb and when I run the sp_spaceused command for tempdb I notice that the unallocated space is a negative number. I have not received any errors in the error log with regards to expanding the segment because segment is full. Is this just a bug in SQL 6.5 or something else?

View 2 Replies View Related

Query Returns No Row

Feb 23, 1999

When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you

View 2 Replies View Related

Query Returns Different Values?

Dec 10, 2012

I have written sql query

select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +

[Code].....

I am getting different amount 984000.0000 and quantity 9.

View 1 Replies View Related

Query Returns More Records Than Expected

Nov 29, 2012

I am having a query

select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber

tarrifheadno SUM(INVOICEITEMS.ItemQuantity)

84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20

tarrifheadno is unique

Now if i use below query and add invoicetypecode field

select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode

This query return distinct of 11 records

84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2

How to get same 7 records

View 1 Replies View Related

T-SQL (SS2K8) :: BCP Query-out Returns No Records?

Sep 3, 2014

I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.

I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:

DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)

[code].....

The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:

output

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL

If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.

I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.

View 1 Replies View Related

I Want A Query That Returns Only The Column Names

Dec 16, 2007

I want a query that returns only the column names

View 4 Replies View Related

SubQuery Returns More Than One Row - Update Query

Sep 21, 2006

Hello,

I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.

Here's my query:

UPDATE temp_UpdateRemainingHours
SET UsedHours =

(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD
FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN
dbo.temp_UpdateRemainingHours ON
dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)

FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours

WHERE
dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD


;




View 5 Replies View Related

Transact SQL :: Same Query Returns Different Result?

Jul 13, 2015

I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.

Select[Field1]
From
(
SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, *
From[dbo].[Table1]
) A
WhereID > 1
OrderBy [Field1]

Those highlighted in yellow colours are duplicate records, I need to remove one of them.

View 8 Replies View Related

Transact SQL :: Query Returns 0 For Calculation

Aug 6, 2015

This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00

Declare @goodtries as int, @badtries as int
select @goodtries = convert(decimal(18,4),count(userID))
from table1
WHERE logintype IN ('Valid', 'Success')
select @badtries = convert(decimal(18,4),count(userID))

[code].....

View 7 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

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 1 Replies View Related

A Simple Query That Returns The Most Current Address

Jul 24, 2007

Hi,I have an Address table which contains more than one addresses for aparticular member. I want to write a query that would only displaymost current address. All addresses have a unique ID (addID).Example:memberID addID address1-------- ------ --------------------------------------------------295 69 13 Auster St295 70 465 Lorre Ct295 71 P.O. Box 321722 171 10 Hannaford Rd722 172 Dubai, United Arab Emirates


Quote:

View 1 Replies View Related

Parameterized Query Returns One Row With Null Values.

Jul 28, 2006

I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)"
objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!

View 7 Replies View Related

Query That Returns Record That Arn't Duplicated In Another Table

Jul 20, 2005

Hi.I have a table (websitehits) which holds statistics about websites.This table has a date field (datecounted). What I need is to create aquery which returns a list of dates between two date ranges (say ayear ago from today and a year from now) which only shows dates thathaven't been used in the websitehits table for a given website.For example if my table contains something like:Website Datecounted HitsSite101/01/046000Site101/02/046500Site101/03/046250Site201/03/041000Site201/04/041200Site201/05/041500So if query for ‘site1' then I'd get a list of all dates between30/11/03 to 30/11/05 with the exception of the dates 01/01/04,01/02/04 and 01/03/04.So far I've tried to do this using another table named calendar whichcontains a very long list of dates and to use this to produce the list– but I'm not getting very far.By the way I'm using sql server, an I need this query to generate alist for an asp page - so I need to pass the website name as aparameter so I guess I need to make this query as a stored procedure.Does anyone know how this can be done?

View 1 Replies View Related

Sql Query To Update A Column When The Subquery Returns More Than One Row

Oct 6, 2006

Hi People,

I am having a table which has some 10 cols, only one column had all Nulls. DB-SQL2K5

I am now writing a query like

Update Test1

set Id =

(Select t2.Id from

Test2 t2, Test1 t1

where

t2.Name = t1.Name)

as likely this query is faling as the sub query is retuning more than a row. What is the best method to achive my requirement?

Thanks

View 7 Replies View Related

Aggregate Task When Query Returns Empty.

Jan 13, 2008



To create a strike file for a text file output, I piped the output of a query throught a mulitcast. One output is the actual flat file, the other output is the strike file. The next task for the strike output is an aggregate count and sum functions. If the original query returns no records, the count shows zero, but the sum shows NULL. Further down the process I have to pad out the values with zero's, but that NULL is causing problems. How do I convert the Null to either an empty string, or a value of zero?
Thanks

View 1 Replies View Related

Data Mining Query Returns ADOMDDataReader

May 22, 2006

Hello,

I created a Market Basket Data Mining Model with Association Rules, which I want to query and show in a Report. Everything works fine, when I preview the result in the Reporting Services Data tab I see some sort of table which I can expand and then see the related products.

Unfortunately this result seems to be an ADOMD Datareader which I cannot place in a table, matrix or textfield.

If anyone knows how I can make this Informationen available in my Report please let me know.

Thanks in advance

View 6 Replies View Related

SUM Returns Incorrect Value In SQL Server Mobile Query ... Bug??

Feb 6, 2007

Hi all,

I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.

I am running Visual Studio 2005 and have created 2 tables:

Orders and OrderLines which are set up in master detail fashion.

The SQL Statement I create in the Query Builder is as follows:


SELECT     Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM         Orders, OrderLines
WHERE     Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address

Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo

Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???

Here are a couple of screenshots which will demonstrate what I mean.

Here is the contents of the Orders and OrderLines tables.  Each order has only 1 line item in it.

 http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html

When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.

http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html

I hope this explains it better.

Anyone have any ideas??? 

Thanks in advance

 

View 13 Replies View Related

SqlDataSource Returns Fewer Results Than Original Sql Query

Aug 3, 2007

I have a query that I created in SqlServer and then merely copied the code to a sqldatasource. They are both connected to the same db, however the sqldatasource query only returns 6 records and when run in SqlServer, it returns 52 rows which is the correct number.
 Any ideas on what might be causing this? There are no filters on the sqldatasource. It only has that one query. Do they differ in the way they execute sql code?
 Thanks for any help!

View 1 Replies View Related

SQL 2012 :: Query Returns 13864 On A Varchar Columns

Mar 24, 2015

We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.

[Number] [varchar](15) NOT NULL
[Person_ID] [varchar](12) NULL

Here is the query we are doing:
Select Number,Person_ID From TableName where LP='ABC123'

The result I get back is the following:
Number:1
Person_ID:13864

The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.

When we do this query on our copy we get back
Number:1
Person_ID:

Which is exactly what we should see as the result.

Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?

View 2 Replies View Related

Transact SQL :: Query That Returns Material (items) That Are Used In Event On Certain Day - RIGHT JOIN

Nov 29, 2015

I have a query that returns material(items) that are used in an event on a certain day.

SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID

There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.

View 4 Replies View Related

Query To Linked Indexing Service Returns Nothing When Logged In Through SQL Authentication

Nov 7, 2006

I am struggling to get my linked indexing service to return something when I query it. The thing is, when I use Management Studio and log in through Integrated Windows authentication I have no problems. However, when logging in through a test account that only exists in SQL server I get nothing returned. Some observations:
1. Changing the test account to have sa priveliges changes nothing
2. The test account has db_owner role on source db
3. I have tried to create a login using
sp_addlinkedsrvlogin FTIndexWeb, true, 'SomeLogin' but that didn't work. I also tried to do this manually in Mgt Studio.
4. I did not find any authentication related stuff in the management console of my pc in or near the Indexing Service. I'm not sure if my system imposes any security restraints that are invisible to me.
5. My query is
SELECT * FROM OPENQUERY(DNN4_BF_DMX, 'SELECT Path, Rank FROM SCOPE() WHERE FREETEXT(Contents, ''"Confidential"'')')
6. My setup: Win XP (SP 2), SQL SRV 2005 Express, Mgt Studio Express (SP1), (if relevant) .net frameworks 1.1/2.0.

I need to tell my customers how to get this going reliably and now I feel unsure whether this is at all possible. Anyone abe to shine some light on this?

Thanks,
Peter

View 3 Replies View Related

SQL Server 2008 :: Query That Returns Only Rows That Meet Specific Condition?

Oct 22, 2015

Here's what my table looks like;

UniqID | Code |

1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC

I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.

View 9 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

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 5 Replies View Related

I Need Help With A Query And Count

Feb 26, 2007

I have a pretty simple query:
Select title, avg(score) from votes group by title order by avg(score) Desc
This returns a result like this:
Abbey Road 4.0The White Album 3.5Meet the Beatles 3.0
The values in the score field are always 1, 2, 3, 4, or 5
What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received.  So the result set might be like this.
Abbey Road 4.0 0 0 1 1 1 The White Album 3.5 0 1 0 0 1Meet the Beatles 3.0 2 2 2 2 2
I have tried to use the count function, but it counts all the values in the field.  I can't figure out how to just count the values that match a certain criteria.  When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets.
Any suggestions would be greatly appreciated,
Chris

View 1 Replies View Related

Sql Count Query

Jun 2, 2008

I am trying to count a column in my tbl.  I have a table with the following,  What i am trying to do is count the number of days of dtAttendance for an individual where bitpresent  = 1.  So this individual should have 2 days of attendance.  I wrote some code belwo but it does not seem to count correctly, some it does and some it does not.
intAssignedPersonnelID       intUICID       strSSN       dtAttendance       bitPresent      intDrillStatus      intMileage     bitDatePayed210                                         1         333333333          5/26/2008            1                      2  210                                         1         333333333          5/27/2008            1                      2  210                                         1         333333333          5/28/2008            0                      2
 
& "(select count(*) from saddotnet.dbo.tblAssignedPersonnel h where p.strSSN = h.strSSN and h.dtattendance <= p.dtattendance) " _
& "as count_attendance_as_at_dtattendance, p.dtattendance, bitpresent, intdrillstatus, " _
& "'<input name=chbxPresent' + convert(varchar(10), p.intAssignedPersonnelID) + ' type=checkbox>' as 'theCheckBox' " _
& "from cms.dbo.tblSIDPERS as s INNER JOIN " _
& "saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where sidstrSSN_SM = left('" & SSN & "', 9)"
              

View 2 Replies View Related

How To Query COUNT

May 22, 2004

If I want to find out how many items of a field is in a table like Type1, Type2... to build a table like

Type1, 10
Type2, 25
...

Is is better to open the table -> Select Type From Table

and then go down each row and record the count of a type or is it better to

Select Distinct Type from table to get the types and then do each type's select to get the count

Select Count (1) from table where type = 'Type?'

Thanks,


Frank

View 1 Replies View Related

Query Row Count

Nov 3, 2005

I tried searching, but no luck.

I am trying to return a row count of a query...

I have a table that is grouped by a date.  Some records have the
same date.  I am just trying to return the total row count of the
query.

Here is the sql:

SELECT  ActiveWeek
FROM     wcWARS
WHERE     CreatedBy = 9034 AND FlagComplete = 1
GROUP BY ActiveWeek

I just want to return the total grouped column.... I thought Oracle had
a ROWCOUNT function that returned the total rows of the query.
I was unable to find one for SQL Server. 

**Note:  I already tried @@ROWCOUNT ~ This returns the total rows of the table.

Thanks
Larry

View 4 Replies View Related

SQL Count On A Sub Query

Dec 20, 2005

Hello All,
I have two tables A and B.
A and B have column x in common. The relationship between A and B is one to many.
I would like to count the number number of x's in table B and group by the x and then join this result back to table a.
My attempt :
SELECT COUNT(T1.x), T1.x, A.meeting_invite_idFROM (SELECT meeting_invite_idFROM REMINDER) T1, AWHERE A.meeting_invite_id *= T1.meeting_invite_idGROUP BY A.meeting_invite_id
Anyone with any ideas.
 
 

View 1 Replies View Related

How To Use Count In Mdx Query

Nov 9, 2006

hello people... sorry im new with this stuff, cant get it right... i need help...

suppose i have a table that has the stores in columns and and order count as a row:

Store1 Store2 Store3 Store4
ordercount 100 null null 30

assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved