I know this is simple but my minds gone blank
id desc
12345 Item 1
12345 Item 2
12346 Item 3
12346 Item 4
12347 Item 5
I want to count all the distinct ID's, so for the results here I need
id count
12345 2
12346 2
12347 1
Thanks
Hi;
I know it must be really easy but I couldn't somehow figure it out:
3 columns: pms_ID, pms_UserID, pms_FlagSeen 1 ab1 True 2 ab1 True 3 ab1 True 4 ab1 False 5 ab2 True 6 ab2 False
All I want is to count number of Falses and Total (Trues and Falses) belonging to Users (I mean by pms_UserID)
Any help would be appreciated..
Regards...
Hi, i am using MSSQL 2005 and i am trying to count the number of rows in a table that is null. The problem is that its returning 0 even doh its several that is null.
SELECT count(*) as nr FROM forum_board WHERE (owner = null)
have also tried
SELECT count(*) as nr FROM forum_board WHERE (owner = 'null')
still returns 0.
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate
from tblCsEventReminders
where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
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
found it
View 3 Replies View RelatedHey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
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
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 5 Replies View RelatedThe 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
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies View RelatedI would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
...when I started this endeavor.
I have a previously developed Lotus Notes App. The idea was simple; as
I am sefl taught on Lotus Script, I figured I'd be able to stumble my
way through VB.Well,
it started OK. I used VB Express to get familiar with the stuff, but
decided to go with a full version of VS 2005 and try and get this thing
properly developed as a web app. I purchased several reference books
etc., and have become relatively familiar with the forums here.First
issue I have is that I simply want to use code to update or add records
to an SQL DB. I know about datagridview etc., but I want to update the
DB using forms, not the tabular view those controls provide. I thought
it would be relatively straight forward, but found my ignorance runs
deeper than I thought. When I tried to do so I am finding I am not
really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The
issue with searching these forums is most posts deal with datagridview
or something similar.I have spent a ton of time trying to find relevant
posts or articles, but have had no luck yet.Again, all Ireally
need is a nudge in the right direction. I am more than willing to plod
through reference materials or articles/posts to find what I need to
know, I just can't find where to even start on the info I need. Regards, Joe
Hi
I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work.
ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
How can i get an output like this from this sql??
HEM_PATIENT_ID is primary key...
Output:
First row: initial values of the fields
Second row: average of the same fields
Please help me...
select * from (
select HEM_LOKOSIT, HEM_NNS
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33
union
select AVG(HEM_LOKOSIT), AVG(HEM_NNS)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33)
order by HEM_LOKOSIT desc nulls last;
Am new to SQL Server , can any1 tell me is there anyway to display date in this format month and year like Apr 2000 ,excluding the day.
eg: 01/28/2000 should b displayed like Jan 2000
Hi,
I'm relatively new to SQL7 but I did use 6.5 a fair bit.
I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table
2)Add some data to the test table
3)Back up the transaction log
4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Thankyou,
Tim
Given one table with one column and two rows, one containing the string 'Bill', one containing the string 'Gates'.
Write a select statement which gives you the result 'Bill Gates'.
???+
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
Hi,
I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function
for this in MS SQL?
I need somethgn like
a 1,2,3
b 4,5
c 7
d 5,55,5
I can do that with creating 2 cursors but looking for some easy way around.
Any suggestion and help highly appretiate.
Thanks
incorrect syntax near #
how do i fix this and did i make any other errors?
SELECT Master.CheckNum, Master.CheckDate, Master.ExpenseType, Deal.Alias, Detail.InvoiceDate, Detail.InvoiceAmount, Detail.Person, Detail.Deal, Detail.InvoiceNum, Detail.Reference, Detail.idDetial
FROM Master INNER JOIN (Deal INNER JOIN Detail ON Deal.Deal = Detail.Deal) ON Master.CheckNum = Detail.CheckNum
WHERE (((Master.CheckDate)>#12/5/2005#) AND ((Deal.Alias)="aic"));
Originally i had:
DELETE FROM #RptDetails WHERE StructureType <> @StructureType
AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
But it didnt delete the structure types i changed to :
DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND
DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?
Hi,
How do I sum all of the returned values into my output param ? This returns multiple rows, all data is oftype decimal.
Thanks
Bob
ALTER proc
spPSICalcA9
@iReturn int output,
@Contract varchar (8)
as
Select
sd.HoursLostRain,
sd.HoursLostMaxT,
sd.HoursLostMinT,
sd.HoursLostFrost,
sd.HoursLostWind,
sd.HoursLostVis
from
SiteDiary sd
where
sd.Contract = @Contract
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
Hi,
I need to return all records where ..
Contract = @Contract
AND
CrossReference is null or ""
I have this but I dont think its right..
Bob
where
a.Contract = @Contract AND a.crossreference is null OR a.crossreference = ""
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
I think it's simple, but I can't get it to work.In English its: find records in TableA where the field [Field1] hasmore than one unique value in Field2sample records in TableAField1 Field22241 123452241 123452242 123452242 99856desired return (2 records)2242 123452242 99856thank you for your helpPaul
View 3 Replies View Related