In SQL 2000 Can I Use Count() To Count A Column?
Nov 26, 2007
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 5 Replies
ADVERTISEMENT
Apr 29, 2008
the Table columns is like this NO ProductNo Area In Out1 0001 US NULL NULL2 0002 UK NULL Y3 0003 FR Y NULL 4 0004 FR Y NULL5 0005 UK Y NULL I have Query get the result belowArea Count In&OutUS 1UK 2FR 2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is Null , if one of them is Null the plus 1 so the result would like I have Query get the result belowArea Count In&OutUS 1 1
UK 2 1
FR 2 0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you
View 4 Replies
View Related
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
May 25, 2015
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.
View 3 Replies
View Related
Jul 3, 2013
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'
View 9 Replies
View Related
Jul 23, 2005
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 Related
Jan 22, 2008
HistoryLog Table
MainIndex
Sales
Date
1
John
Jan-05-1999
2
John
Jan-07-1999
3
Liz
Jan-08-1999
4
Yumi
Jan-08-1999
HistoryLogDetail Table
DetailIndex
MainIndex
Date
1
1
Jan-05-1999
2
1
Jan-07-1999
3
2
Jan-08-1999
4
3
Jan-08-1999
select * from HistoryLog I will got a return result with Mainindex �Sales�Date 3 kinds of columns Data , If I need to add one column by myself let the result like below Mainindex �Sales�Date�Total the Total's value is from count(MainIndex) of HistoryLogDetail Table , result will look like below MainIndex Sales Date Total1 John
Jan-05-1999 2 →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 2)2 John
Jan-07-1999 1 →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 1)3 Liz
Jan-08-1999 14 Yumi
Jan-08-1999 0 I trid wirte a code to got the total column but what I got is like thisMainIndex Sales Date Total
1 John
Jan-05-1999 4
2 John
Jan-07-1999 43 Liz
Jan-08-1999 4
4 Yumi
Jan-08-1999 4 what the columns of Total shows is .. how many record are in HistoryLogDetail Table but that's not I need ..can you help?thank you very much
View 7 Replies
View Related
Oct 29, 2007
Hey there,
I am new to SQL so please bear with me. I was wondering how to create a new column that counts the EntityId COUNT(CdsQuotes.EntityId) and puts it into a seperate column.
What I am trying to achieve is a count of entity IDs from one table and a count of entity IDs from another table - but the 2nd count must be from the first counts results (hope this makes sense).
So it returns a count from table 1 and then a corresponding count from table 2 (based on table 1 entityIDs)
Any ideas?
SELECT
Entity.EntityName,
SuspectBond.EntityId,
COUNT(SuspectBond.EntityId) as "Suspect Bonds"
FROM SuspectBond
INNER JOIN Entity ON
SuspectBond.EntityID = Entity.EntityId
WHERE
SuspectBond.StatusId = 1
GROUP BY
SuspectBond.EntityId,
Entity.EntityName
ORDER BY
COUNT(SuspectBond.EntityId) DESC
View 6 Replies
View Related
May 6, 2008
hi,
I have a table as below
id col2 col3 col4 col5 col6 col7
1 2 2 2 2 3 4
2 3 6 2 2 2 5
3 2 3 4 2 2 6
4 3 2 2 2 9 8
5 2 3 4 6 2 2
I want to know the number of columns which is having the value 2, in each row
can anyone help?
View 2 Replies
View Related
Apr 24, 2008
Hi I have added a new column to my table, i need to run a query and update this colmn with a count, so like
i = 1
loop
update table
set column = i
where column = 113
i=i+1
loop
just not sure what the sql syntax is?
View 20 Replies
View Related
Nov 3, 2013
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.
View 5 Replies
View Related
Oct 26, 2007
I've a nub question that someone probably has the fast answere too.
How do I count the number a columnvalue in a table has changed? I was starting to write a stupied cursor but there has to be a much smarter way.
I've a case where I need to count the number of times the salary in a table for individual employees changes.
Thanks in advance!
View 2 Replies
View Related
Jul 2, 2015
I have table like below
col1 volume
A 22
B 28
C 1
E 3
D 4
Output should be like below
col1 vol
A 22
B 28
Others 8
View 4 Replies
View Related
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
Apr 22, 2008
I have a table for blog comments I want to add a column that counts the number of comments for each article.
existing table looks like this:
CommentID
ArticleID (FK)
commentAuthor
authorEmail
comment
commentDate (getDate())
I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool:
"SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)"
But can I add that to the table somehow so it does it automagically???
View 5 Replies
View Related
Jun 16, 2008
I m using SQL Server 2000.
I have Tabel named Topic and have a column name lineage. lineage has data like following:
//////546707//546707//546707/43213/
Now I want to get records who has only one "/" in it's crreponding lineage column.
Can somone tell me how to do that in SQL Server 2000?
Thanks
Khushbu
View 2 Replies
View Related
Sep 5, 2012
How do I count the number of alike values in a column and have that value as a count column.
Example using Col3:
Col1 Col2 Col3
A 12 Test
B 45 Test1
C 45 Test
D 12 Test
E 10 Test1
F 11 Test2
Result
Col1 Col2 Col3 Count
A 12 Test 3
B 45 Test1 2
C 45 Test 3
D 12 Test 3
E 10 Test1 2
F 11 Test2 1
View 6 Replies
View Related
Apr 2, 2008
Assuming that I have a table similar to this simplified version:
CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));
How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:
field1 count
some value 24
another word 20
a value 5
(and so on...)
How can I do this?
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
View 3 Replies
View Related
Oct 24, 2013
I am having following data:
c1c2c3
122401
1221072
122833
122793
122813
122314
[code]....
I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
Output
c1p1p2p3
122933
View 6 Replies
View Related
Mar 24, 2014
let us suppose
ID | companycode | year | name | State
1 | 01 | 2013 | xyz | D
1 | 01 | 2013 |abs | S
23| 02 | 2014 | age | D
2 | 01 | 2013 | XYZ | D
1 | 01 | 2013 | abh | D
7 | 13 | 2014 | adi | C
7 | 13 | 2014 | adi | C
7 | 13 | 2013 | adi | C
7 | 13 | 2014 | adi | C
7 | 13 | 2013 | adp | C
7 | 13 | 2014 | abp | C
and we want like this
ID | companycode | year | countname | State
1 | 01 | 2013 | 2 | D
23| 02 | 2014 | 1 | D
1 | 01 | 2013 | 1 | s
2 | 01 | 2013 | 1 | D
7 | 13 | 2013 | 2 | C
7 | 13 | 2014 | 4 | c
View 4 Replies
View Related
May 18, 2014
My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.
e.g. TABLE2:
e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5
UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);
--^Doesn't work
so my TABLE1 should be:
e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0
(The .. is just spacing to show the table here)
View 1 Replies
View Related
Mar 3, 2015
I have a few million rows of a data in a table that store indexing information for scanned customer documents. Fields like last name, first name, document description (descript), document type and scan date (scandate).
I want to be able to query the document description column where it'll show how many times each document description was scanned/used over the course of the last 6 months or year.
View 3 Replies
View Related
Jul 20, 2005
I am trying to determine the number of columns that are returned froma stored procedure using TSQL. I have a situation where users will becreating their own procedures of which I need to call and place thoseresults in a temp table. I will not be able to modify those usersprocedures. I figure if I have the number of columns I can dynamicallycreate a temp table with the same number of columns, at which point Ican then perform an INSERT INTO #TempTableCreatedDynamically EXEC@UserProcCalled. With that said, does anyone have any idea how todetermine the number of rows that an SP will return in TSQL?Thanks!
View 2 Replies
View Related
Mar 20, 2008
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------
SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek,
COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration
WHERE Archived = 0
GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3))
ORDER BY
CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7
END
--------------
returns
MON 6
TUE 3
WED 5
THU 3
FRI 2
SAT 1
View 4 Replies
View Related
Dec 6, 2007
i have a table with productID and OrderID. For ech product there are orders.
So for each productID there are lot Of OrderID's are present.
My data like
ProductID OrderID
1 12012
1 12447
1 12478
2 24154
2 21457 etc.......
so, i need to get the maximum count for a product.
i can do by using Temporary tables. but i need without using temporary tables.
please help me out.
thanks
-Praveen.
View 12 Replies
View Related
May 16, 2007
Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005?
View 4 Replies
View Related
Oct 25, 2004
Hello all, I'm creating a stored procedure in SQL Server 2000. I'm trying to count how many rows match a certain WHERE clause, and assigning this integer to a variable:
----------------
DECLARE @RowCount int,
SELECT COUNT(*) FROM Customers WHERE FirstName='Joe'
----------------
How can I assign the count that returns from the SELECT statement to the @RowCount?
Thanks
View 2 Replies
View Related
Jan 31, 2005
Hi folks!
The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.
The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.
I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:
So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.
For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.
Here is the code I think (sawdust) can be used:select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns
where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns
where table_name = 'Dest')) as ColumnDiff
Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.
Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.
Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?!
Paul
View 4 Replies
View Related
Jul 27, 2006
Hello folks,
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table:
Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
Thanks for your help
Mike
View 2 Replies
View Related
Jul 10, 2013
What i want to do is count how many times the [Omnipay_Account] column is populated with a number per parentid.
In the [Omnipay_Account] column you can either have a null or a 15 digit number
Idea result layout would be three columns these columns would be
Every row, would need to be grouped via the parentid number
ParentId Omnipay MSIP
My query is
SELECT
[ParentID]
,[Omnipay_Account]
FROM [FDMS].[dbo].[Dim_Outlet]
View 3 Replies
View Related
Feb 3, 2008
Hi,
I have a table employee with 4 columns,
empno fname lname deptno
1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50
suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?
select count(deptno) from employee
where deptno IS NULL..
When i query this i get the result as 0..
View 8 Replies
View Related
Jan 15, 2007
Hello
I'm reading a XML file and the next operation need a column with the row count.
I don't want to know how many rows there is in the recordset, but, the row count of each record.
How can I implement this?
tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal
View 1 Replies
View Related
Nov 8, 2007
Hi,
I have one data flow control. Source is SQL server and destination is flat file destination. I have one derived column placed in between these two. This functionality works fine. I would like to sum one column data and count total no. of columns and put it in global variable. How can I achieve it?
Thanks,
View 7 Replies
View Related
