How To Count Column ?
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 Total
1 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 1
4 Yumi
Jan-08-1999 0
I trid wirte a code to got the total column but what I got is like this
MainIndex Sales Date Total
1 John
Jan-05-1999 4
2 John
Jan-07-1999 4
3 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
ADVERTISEMENT
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
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 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
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
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
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
Jul 22, 2015
Below are the columns I have in table Customers.
Date CustomerID
7/14/2015 AAA
7/14/2015 BBB
7/15/2015 AAA
7/15/2015 BBB
7/15/2015 CCC
7/16/2015 AAA
7/17/2015 AAA
7/18/2015 AAA
7/19/2015 AAA
What I need is to find running sum of count of customerIDs. here CustomerID is string. So, the expected result is
Date Run_Sum
7/14/2015 2
7/15/2015 5
7/16/2015 6
7/17/2015 7
7/18/2015 8
7/19/2015 9
View 6 Replies
View Related
Jan 20, 2008
how count column in pivot table- and add result row
i need to calculate each column
for example
day1 day2 day3 day4 day5
-------------------------------------------------------------------------
1 2 1 2 3
1 2 3 2 2
2 3 2 1 2
2 3 0 0 0
-----------------------------------------------------------new result row
ok ok 1|2|3 1 3
i need to check each column
if i have twice each number
if not show the missing number
TNX
Code Block
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080301', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @Numdays > 28
BEGIN
SELECT p.ID,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END
View 12 Replies
View Related
Oct 30, 2014
I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.
View 9 Replies
View Related
Jul 23, 2005
I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn
View 2 Replies
View Related
Aug 28, 2006
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
here is my stored proc
CREATE PROCEDURE dbo.ExportFile
@intMonth INT,
@intYear INT,
@dtFirstDayOfMonth DATETIME
AS
BEGIN
BEGIN TRANSACTION
/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/
UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @@ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT
IF @@ERROR<>0
GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
END
ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO
View 8 Replies
View Related
